SQL Query

I want to make every that satisfy my condition to = 1400.

I wrote:

Select GLDV_Alt
Case
When gldv_alt_div_n = '1209', '1120' , '1401'
then '1400'
Else 0
End As 'Region South
from gldv


This works if my list is just one division, but not when it's a list of divisions.

Do I have to do a bunch of When operators?

Please help.
LVL 1
isamesAsked:
Who is Participating?
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Here you go ... also made multiple syntax corrections
Select GLDV_Alt, 
Case When gldv_alt_div_n IN ( '1209', '1120' , '1401') then '1400' Else 0 End As [Region South]
from gldv

Open in new window


For more info on CASE blocks check out my image and demo-heavy tutorial called SQL Server CASE Solutions
0
 
ste5anSenior DeveloperCommented:
Yup, that's a possibility. Or use the IN clause:

SELECT  GLDV_Alt ,
        CASE WHEN gldv_alt_div_n IN ( '1209', '1120', '1401' ) THEN '1400'
             ELSE 0
        END AS 'Region South'
FROM    gldv;

Open in new window

0
 
Scott PletcherSenior DBACommented:
The final choice would be to use a lookup table.  The only real advantage of this method is less chance for error if the list of numbers changes and/or becomes more complex, as the query itself doesn't change, just the values loaded into the table.  For a very large gldv table, you might even notice some performance improvement.

CREATE TABLE #GLDV_Div (
    gldv_alt_div_n varchar(4) NOT NULL,
    result varchar(4) NOT NULL
    )
INSERT INTO #GLDV_Div VALUES('1209' , '1400')
INSERT INTO #GLDV_Div VALUES('1120' ,  '1400')
INSERT INTO #GLDV_Div VALUES('1401'  , '1400')


Select GLDV_Alt, isnull(lookup.result, '0') As 'Region South'
from gldv
left outer join #GLDV_Div lookup on lookup.gldv_alt_div_n = gldv.gldv_alt_div_n
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
isames, do you still need help with this question?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.