Case statement not working

I have a Case statement that is working for the first "when", but not the
second.
t1.CsxShortDesc,
CASE WHEN t1.CsxShortDesc = 'SDICA' THEN 'D'
WHEN t1.CsxShortDesc = 'ST1CA' THEN 'I' ELSE NULL END
in the above statement the t1.CsxShortDesc works fine.
In the Case statement, where the same field is used (CsxShortDesc), it works
for the first WHEN. If the value is 'SDICA', it will display a 'D', but if
it is equal to 'ST1CA', it does not display 'I'. It is Null (the ELSE
part).
Both whens are looking at the same field, so why doesn't it work?
Thanks,
TomCan you provide an actual repro (DDL, sample data)?
Is CsxShortDesc CHAR or VARCHAR?
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23mk5e5gvFHA.3756@.tk2msftngp13.phx.gbl...
>I have a Case statement that is working for the first "when", but not the
>second.
> t1.CsxShortDesc,
> CASE WHEN t1.CsxShortDesc = 'SDICA' THEN 'D'
> WHEN t1.CsxShortDesc = 'ST1CA' THEN 'I' ELSE NULL END
> in the above statement the t1.CsxShortDesc works fine.
> In the Case statement, where the same field is used (CsxShortDesc), it
> works for the first WHEN. If the value is 'SDICA', it will display a 'D',
> but if it is equal to 'ST1CA', it does not display 'I'. It is Null (the
> ELSE part).
> Both whens are looking at the same field, so why doesn't it work?
> Thanks,
> Tom
>|||typo? - should it be 'STICA' instead of 'ST1CA'?
If not, do you, in fact, have rows matching the criteria being used that
have a csxshortdesc value of ST1CA?
tshad wrote:

>I have a Case statement that is working for the first "when", but not the
>second.
> t1.CsxShortDesc,
>CASE WHEN t1.CsxShortDesc = 'SDICA' THEN 'D'
> WHEN t1.CsxShortDesc = 'ST1CA' THEN 'I' ELSE NULL END
>in the above statement the t1.CsxShortDesc works fine.
>In the Case statement, where the same field is used (CsxShortDesc), it work
s
>for the first WHEN. If the value is 'SDICA', it will display a 'D', but if
>it is equal to 'ST1CA', it does not display 'I'. It is Null (the ELSE
>part).
>Both whens are looking at the same field, so why doesn't it work?
>Thanks,
>Tom
>
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eAlu88gvFHA.2504@.tk2msftngp13.phx.gbl...
> Can you provide an actual repro (DDL, sample data)?
> Is CsxShortDesc CHAR or VARCHAR?
I will look at it.
The problem is that I will need to recreate it as a test table and select
statement, because this is a large Select with 4 views being created and
reading a table of about 20,000 records.
This is a VARCHAR. What is confusing is both are 5 characters and SDICA
works why doesn't ST1CA? ST1CA is being displayed in the field before the
Case, so I know that is correct.
Tom
>
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:%23mk5e5gvFHA.3756@.tk2msftngp13.phx.gbl...
>|||Can you post table schema?
This works for me.
select
t1.CsxShortDesc,
CASE
WHEN t1.CsxShortDesc = 'SDICA' THEN 'D'
WHEN t1.CsxShortDesc = 'ST1CA' THEN 'I'
ELSE NULL END
from
(
select 'SDICA'
union all
select 'ST1CA'
) as t1(CsxShortDesc)
AMB
"tshad" wrote:

> I have a Case statement that is working for the first "when", but not the
> second.
> t1.CsxShortDesc,
> CASE WHEN t1.CsxShortDesc = 'SDICA' THEN 'D'
> WHEN t1.CsxShortDesc = 'ST1CA' THEN 'I' ELSE NULL END
> in the above statement the t1.CsxShortDesc works fine.
> In the Case statement, where the same field is used (CsxShortDesc), it wor
ks
> for the first WHEN. If the value is 'SDICA', it will display a 'D', but i
f
> it is equal to 'ST1CA', it does not display 'I'. It is Null (the ELSE
> part).
> Both whens are looking at the same field, so why doesn't it work?
> Thanks,
> Tom
>
>|||"Trey Walpole" <treypoNOle@.comSPAMcast.net> wrote in message
news:eNpYd%23gvFHA.2556@.TK2MSFTNGP15.phx.gbl...
> typo? - should it be 'STICA' instead of 'ST1CA'?
> If not, do you, in fact, have rows matching the criteria being used that
> have a csxshortdesc value of ST1CA?
Checked that out.
Here is part of the results pane:
ST1CA NULL 94.43 SDICA D 22.19
Tom
> tshad wrote:
>|||"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:C6CB3220-A533-4FB7-9738-D61964128AE9@.microsoft.com...
> Can you post table schema?
Not really.
This is a foreign system with a lot of tables. I am creating creating 4
Views and joining them with 3 other tables to get it.
I will have to create a test table and try to recreate it there.

> This works for me.
> select
> t1.CsxShortDesc,
> CASE
> WHEN t1.CsxShortDesc = 'SDICA' THEN 'D'
> WHEN t1.CsxShortDesc = 'ST1CA' THEN 'I'
> ELSE NULL END
> from
> (
> select 'SDICA'
> union all
> select 'ST1CA'
> ) as t1(CsxShortDesc)
Right.
But in my case, it isn't. As you can see from my results in the other post,
it isn't doing the 'I', even though it is obviously ST1CA.
I am going to try reversing case and put ST1CA and see if I get the same
results.
Thanks,
Tom
>
> AMB
>
> "tshad" wrote:
>|||"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:OImCiLhvFHA.3252@.TK2MSFTNGP10.phx.gbl...
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:C6CB3220-A533-4FB7-9738-D61964128AE9@.microsoft.com...
> Not really.
> This is a foreign system with a lot of tables. I am creating creating 4
> Views and joining them with 3 other tables to get it.
> I will have to create a test table and try to recreate it there.
>
> Right.
> But in my case, it isn't. As you can see from my results in the other
> post, it isn't doing the 'I', even though it is obviously ST1CA.
> I am going to try reversing case and put ST1CA and see if I get the same
> results.
OK, I tried that and it definately has to do with the position of the WHEN
statement and not the Data. I found the error in my code. The problem is I
have 20 of this statements (for 30 columns in a CSV file). The first one
works fine. Here is the 2nd one. It turns out 2-20 all refer to the table
t1 instead of their respective tables t2 -> t2, t3 -> t3.
t2.CsxShortDesc,CASE WHEN t2.CsxShortDesc = 'ST1CA' THEN 'I'
WHEN t1.CsxShortDesc = 'SDICA' THEN 'D' ELSE NULL END,t2.CsxCurrTax,
I did a lot of copying and pasting here and just missed this one - no matter
how much I stared at it.
Thanks,
Tom

> Thanks,
> Tom
>