Question an update using case or an if statement 1

[UNCMoochie]

I'm trying to rebuild and reorganize a database table. This table i'm working off of has a column for every year going back to 1973 and the total number of goods purchased by a customer in each column (it's very unorganized). There isn't a customer file and I'm trying to create one with a date joined (or something equivalent).
I tried using case at first B/C I wasn't sure if it worked as an elseif checking each condition even if it had met the first, but that didn't work. i want to do something like below (only what i have below doesn't work) let me know if i'm being unlclear.

Create Table #T1 (Id Int, s98 Int,s99 Int, s00 Int, yr_joined varchar(4))
Insert Into #T1 Values(1, 206,65,11,'')
Insert Into #T1 Values(2, 0,23,11,'')
Insert Into #T1 Values(3, 0,0,11,'')
Insert Into #T1 Values(4, 99,23,11,'')
Insert Into #T1 Values(5, 614,0,11,'')

update #T1 set yr_joined =
case
when s00 > 0 then '2000'
when s99 > 0 'Green' then '1999'
when s98 > 0 not null 3 then '1998'
end


is there a way to make this work similar to an Elseif statement?

 

[vbkris]

hi,

the case statement generally works like the elseif statement, it executes the first true clause and exits.

what exactly is not working? what is your expected output???

Known is handfull, Unknown is worldfull

 

[UNCMoochie]

ignore the 'Green' i typed that accidentally same thi g with the line below... what i actually had was:

Create Table #T1 (Id Int, s98 Int,s99 Int, s00 Int, yr_joined varchar(4))
Insert Into #T1 Values(1, 206,65,11,'')
Insert Into #T1 Values(2, 0,23,11,'')
Insert Into #T1 Values(3, 0,0,11,'')
Insert Into #T1 Values(4, 99,23,11,'')
Insert Into #T1 Values(5, 614,0,11,'')

update #T1 set yr_joined =
case
when s00 > 0 then '2000'
when s99 > 0 then '1999'
when s98 > 0 then '1998'
end

 

[UNCMoochie]

when i execute this code, i get
id s98 s99 s00 yr_joined
============================================
1 206 65 11 2000
2 0 23 11 2000
3 0 0 11 2000
4 99 23 11 2000
5 614 0 11 2000
for the result set... when it should be (or i should say I want it to be)
id s98 s99 s00 yr_joined
============================================
1 206 65 11 1998
2 0 23 11 1999
3 0 0 11 2000
4 99 23 11 1998
5 614 0 11 1998

 

[monksnake]

This looks strange to me:

update #T1 set yr_joined = 
case 
    when s00 > 0 then '2000'
    [!]when s99 > 0 'Green' then '1999'
    when s98 > 0 not null  3 then '1998'[/!]
end

You are comparing 2 and 3 items at one time, should be like such:

update #T1 set yr_joined = 
case 
    when s00 > 0 then '2000'
    when s99 > 0 then '1999'
    when s98 > 0 then '1998'
end

I'm not understanding the 'Green' and not null and 3.



<.

 

[vbkris]

aargh,

did notice the obvious!!!


Known is handfull, Unknown is worldfull

 

[UNCMoochie]

Thanks for that, i corrected myself above. it was a mistake a made while posting. sorry about that i have to remember to double check before posting... I still have the issue described above though. any help would be greatly appreciated.

 

[amrita418]

UNCMoochie,
Your code was correct, but order of the conditions in the case statement was wrong.
<b>
update #T1 set yr_joined =
case

when s98 > 0 then '1998'
when s99 > 0 then '1999'
when s00 > 0 then '2000'
end
</b>

Best Regards,
AA

 

[monksnake]

Yes, amrita is correct, it'll hit the first true condition and stops the case.

<.

 

[UNCMoochie]

thanks amrita,
so the case statement doesn't act as an elseif correct? the second it comes across a clause that matches, it ends?

 

[UNCMoochie]

cool. thanks again

 

[monksnake]

If I'm not mistaken, (and in this case I know I'm not), in any elseIf block, once the first complete condition is met, the block stops executing. So it does treat it the same in SQL.

<.