Case statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Case statement

Author

Topic

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2008-05-05 : 20:27:57

Im running the following sql statement but I dont see the
expected output. There are few differences between acc & cl1, mcc & cl2 , ncr & cl3 but I dont see either 'ONE' or 'TWO' or 'THREE'.
There is even a case where cl3 is null but the sql is not filling in either one or two or three. Query simply returns id & rest as null values.

SELECT P1.id,
CASE
WHEN p1.acc!= p1.cl1 then 'ONE'
WHEN p1.mcc!= p1.cl2 then 'TWO'
when p1.ncr!= p1.cl3 then 'THREE'
Else NULL END
As NOnMatchingColumn
from
(select id, acc, cl1,mcc,cl2,ncr,cl3 from dbo.ml)P1

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-05-05 : 21:51:44

If you are only getting NULL in the second column, then it is failing the first three tests in the CASE statment.

You should be aware that if either column that you are comparing is null, then it will fail the test in the CASE statement.

You need to look at your data carefully to figure out why it is failing the tests.

CODO ERGO SUM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-06 : 01:10:09

quote:
Originally posted by Kumar_Anil

Im running the following sql statement but I dont see the
expected output. There are few differences between acc & cl1, mcc & cl2 , ncr & cl3 but I dont see either 'ONE' or 'TWO' or 'THREE'.
There is even a case where cl3 is null but the sql is not filling in either one or two or three. Query simply returns id & rest as null values.

SELECT P1.id,
CASE
WHEN p1.acc!= p1.cl1 then 'ONE'
WHEN p1.mcc!= p1.cl2 then 'TWO'
when p1.ncr!= p1.cl3 then 'THREE'
Else NULL END
As NOnMatchingColumn
from
(select id, acc, cl1,mcc,cl2,ncr,cl3 from dbo.ml)P1

Can you provide some sample data from your table?

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2008-05-06 : 12:19:21

Michael & Visakh,

I took this to one step further. However Im not handling nulls at all.
Here is the sql.

Either ACC or MCC or NCR or CL1 or CL2 or Cl3 may have & do have nulls in there but they arent even seen in the output.

I request one of you to help me with handling nulls here as Im just clueless on this one.

regards,

Anil Kumar
______________________________________________________________________
select p2.id, p2.NonMatchingColumn,
case p2.NonMatchingColumn
case 'ONE' then p2.acc
case 'TWO' then p2.mcc
case 'THREE' then p2.ncr
end as Pcolumn,
case p2.NonmatchingColumn
case 'ONE' then p2.cl1
case 'TWO' then p2.cl2
case 'THREE' then p2.cl3
end as Scolumn
from
(SELECT P1.id,
CASE
WHEN p1.acc!= p1.cl1 then 'ONE'
WHEN p1.mcc!= p1.cl2 then 'TWO'
when p1.ncr!= p1.cl3 then 'THREE'
Else NULL END
As NOnMatchingColumn
from
(select id, acc, cl1,mcc,cl2,ncr,cl3 from dbo.ml)P1
)P2
Order by p2.id
___________________________________________________________________

What I see in the output...
___________________________________________________________________
ID NonMatchingColumn Pcolumn Scolumn

46 ONE 2008-05-05 00:00:00.000 1999-02-01 00:00:00.000

770 THREE 2008-05-01 00:00:00.000 1899-12-30 00:00:00.000

4874 TWO 2008-05-05 00:00:00.000 1999-11-19 00:00:00.000

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-06 : 12:53:08

IF p1.acc is NULL and p1.cl1 has a value, what is your expected result?

E 12°55'05.25"
N 56°04'39.16"

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2008-05-06 : 12:59:43

Peso -

Thank you for the reply.

Then I would want both of them to be displayed side by side as below.

You also gave me a new possiblity where in both mcc or acc can be null and ncr might not be equal to cl3. I dont know how to handle this either....any help would be greatly appreciated.
________________________________________________________________________________________________
ID NonMatchingColumn Pcolumn Scolumn

46 ONE NULL 1999-02-01 00:00:00.000

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources