CASE - 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

SQL Server 2000 Forums

Transact-SQL (2000)

CASE

Author

Topic

rintus
Starting Member

26 Posts

Posted - 2008-09-17 : 10:03:47

Hi,

I have two columns in the result set and I have to compare the two columns and create a third column using a case condition. Foe example-

I have column A has roll no. from table A and column B has roll no from table B and some of the data in table B is "null" .
To create a third column I used the following syntax.

case when A.roll_no <> B.roll_no then 'No' else 'Yes' end

But when there is a "null" in column B and a value in column A than also its generating a 'Yes'

I want to generate a 'No.' Please suggest.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-17 : 10:06:58

[code]SELECT case when coalesce(A.roll_no,0) <> coalesce(B.roll_no,0) then 'No' ELSE 'Yes' END,.. other columns
FROM TableA a
JOIN TableB b
ON b.linkingcol=a.linkingcol[/code]

rintus
Starting Member

26 Posts

Posted - 2008-09-17 : 10:17:22

I am using DB2 and this doesn't works. the error msg is as follows - The data type, length or value of argument "2" of routine "SYSIBM.COALESCE" is incorrect.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-17 : 10:31:13

quote:
Originally posted by rintus

I am using DB2 and this doesn't works. the error msg is as follows - The data type, length or value of argument "2" of routine "SYSIBM.COALESCE" is incorrect.

This is MS SQL Server forum so all solution post are as per sql server syntax. please post in some db2 forums if you want syntax in DB2.
b/w what's the datatype of roll_no?

rintus
Starting Member

26 Posts

Posted - 2008-09-17 : 10:55:26

The data type used is varchar.....Hope you can help me.....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-17 : 11:06:13

Just change the order of case...

case when A.roll_no = B.roll_no then 'Yes' else 'No' end

E 12°55'05.63"
N 56°04'39.26"

rintus
Starting Member

26 Posts

Posted - 2008-09-17 : 11:17:14