Minor case help - 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 2008 Forums

Transact-SQL (2008)

Minor case help

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2012-04-12 : 08:52:16

I have a two tables that contain the following columns - myID and total_charge.

Using a full join, I am trying to create a MATCH column based on weather the total_charge matches.

Having issues when comparing 0.00 vs null, I want the match column to show "NO MATCH" when comparing these values.

Table A contains the following values
MyID total_charge
1 25.25
2 0.00
3 null

Table B contains the following values
MyID total_charge
1 25.25
1 0.00
2 null
2 0.00
3 55.00

My select shown below.....

select a.*, b.*,
case
when a.total_charge <> b.total_charge then 'NO MATCH'
else 'MATCH'
end as 'MATCH_COLUMN'
FROM a
FULL JOIN b ON a.MyID = b.MyID

Thanks...!

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-04-12 : 10:00:07

try using an equality instead:

select a.MyID as A_ID, b.myID as B_ID,a.total_charge as A_TOT,  b.total_charge as b_tot
, case
when a.total_charge = b.total_charge then 'MATCH'
else 'NO MATCH'
end as 'MATCH_COLUMN'
FROM a
left JOIN b ON a.MyID = b.MyID

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2012-04-12 : 10:53:32

Thanks Don, unfortunately using equal did not help.
Issue seems to be related to comparing a Null value to a value of 0.00

I tried using COALESCE but that did not appear to correct it.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-04-12 : 14:41:12

Funny, it worked for me.


A_ID	B_ID	A_TOT	b_tot	MATCH_COLUMN
1	1	25.25	25.25	MATCH
1	1	25.25	0.00	NO MATCH
2	2	0.00	NULL	NO MATCH
2	2	0.00	0.00	MATCH
3	3	NULL	55.00	NO MATCH

What do you get when you run the code?

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-13 : 05:26:39

Nice 1 Don.
Works for me too.

Vinu Vijayan

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources