Using the case statement to show a particular column code

Question

Using the case statement to show a particular column code

MMITTAL 758

SSC Rookie

Points: 46
More actions
July 1, 2016 at 11:56 am

#319717

Hi,
I'm trying to add a condition in sql where I have used &regiondivision and &divisionid as variables. I want if the case 1st condition is satisfied then the column segmentname should appear else column regionname should appear. Can anybody please confirm the code below is correct or not?
Select REGIONNAME, divisionname, SEGMANTNAME, DIVISIONID, SEGMENTID,
CASE WHEN &REGIONDIVISION <> 'null' AND (&DIVISIONID <> '_FOC_NULL' OR 'Technical' OR 'Non-Technical') THEN SEGMENTNAME
ELSE REGIONNAME END,
sUM(spreadamt) AS SPREAD, FROM TABLEA GROUP BY REGIONNAME, DIVISIONNAME, SEGMENTNAME

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

MMITTAL 758 (7/1/2016)
Hi,
I'm trying to add a condition in sql where I have used &regiondivision and &divisionid as variables. I want if the case 1st condition is satisfied then the column segmentname should appear else column regionname should appear. Can anybody please confirm the code below is correct or not?
Select REGIONNAME, divisionname, SEGMANTNAME, DIVISIONID, SEGMENTID,
CASE WHEN &REGIONDIVISION <> 'null' AND (&DIVISIONID <> '_FOC_NULL' OR 'Technical' OR 'Non-Technical') THEN SEGMENTNAME
ELSE REGIONNAME END,
sUM(spreadamt) AS SPREAD, FROM TABLEA GROUP BY REGIONNAME, DIVISIONNAME, SEGMENTNAME

NULL doesn't compare to anything, so I don't know if your 'null' should really be the word null or NULL the missing value indicator. If the later, you will need WHEN @regiondivision IS NOT NULL.

Also, you can't string multiple value comparisons with OR like you did.

@divisionid NOT IN ('a', 'b', 'c') would be what you are looking for here I think.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

MMITTAL 758 SSC Rookie Points: 46 More actions · Answer 2

Thank you that worked. But now I want to have the distinct values for the column which is satisfied in the case statement and also want to get the condition satisfied for the variable &divisionbox but oracle does not accept Boolean. So how can I put the Boolean condition?

Select CASE WHEN '&REGIONDIVISION' is not null AND ('&DIVISIONID' NOT IN ( 'Non-Technical', 'Technical')) AND '&DIVISIONBOX' = FALSE THEN SEGMENTNAME

ELSE REGIONNAME END as Segment,

sUM(spreadamt) AS SPREAD, FROM table a

GROUP BY CASE WHEN '&REGIONDIVISION' is not null AND('&DIVISIONID' NOT IN ( 'Non-Technical', 'Technical')) THEN SEGMENTNAME

ELSE REGIONNAME END

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 3

MMITTAL 758 (7/1/2016)
Hi,
I'm trying to add a condition in sql where I have used &regiondivision and &divisionid as variables. I want if the case 1st condition is satisfied then the column segmentname should appear else column regionname should appear. Can anybody please confirm the code below is correct or not?
Select REGIONNAME, divisionname, SEGMANTNAME, DIVISIONID, SEGMENTID,
CASE WHEN &REGIONDIVISION <> 'null' AND (&DIVISIONID <> '_FOC_NULL' OR 'Technical' OR 'Non-Technical') THEN SEGMENTNAME
ELSE REGIONNAME END,
sUM(spreadamt) AS SPREAD, FROM TABLEA GROUP BY REGIONNAME, DIVISIONNAME, SEGMENTNAME

Are you executing this statement against Oracle or MS SQL Server?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]