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

SQL Server 2005 Forums

Transact-SQL (2005)

Case Statement

Author

Topic

lisakrebs
Starting Member

10 Posts

Posted - 2010-09-16 : 13:56:00

I have 2 tables and for simplicity sake I'll call them table A and Table B. A column exists in both tables called First_Invoice_date and if that record exists in table B I need to use that date otherwise I have to use the date from Table A. Here is the query I have written but returns Date_First_Invoice not a valid column

Select A,B,C,D,
CASE WHEN TableB.Date_First_Invoiced <> '' THEN TableB.Date_First_Invoiced_AR ELSE TableA.Date_First_Invoiced_AR END AS Date_First_Invoiced
From TableA
LEFT OUTER JOIN TableB ON TableA.ID = TableB.ID
Where Date_First_Invoiced NOT BETWEEN MM/DD/YY AND MM/DD/YY

Any help would be greatly appreciated.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-16 : 14:10:42

left outer join means: if there is no matching row in table b then the values for table b columns are all NULL.
So you can use isnull(b.date_first_invoiced,a.date_first_invoiced)

But to be more clear you should give the table structure, example data and wanted output.

No, you're never too old to Yak'n'Roll if you're too young to die.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-09-16 : 14:12:25

Select A,B,C,D,
--CASE WHEN TableB.Date_First_Invoiced <> ''
-- THEN TableB.Date_First_Invoiced_AR
-- ELSE TableA.Date_First_Invoiced_AR
--END AS Date_First_Invoiced
isnull(TableB.Date_First_Invoiced_AR,TableA.Date_First_Invoiced_AR )
From TableA
LEFT OUTER JOIN TableB ON TableA.ID = TableB.ID
Where Date_First_Invoiced NOT BETWEEN MM/DD/YY AND MM/DD/YY

also, you need to perfix the column in the where clause or sql will tell you it's ambiguous

Jim

Everyday I learn something that somebody else already knew

lisakrebs
Starting Member

10 Posts

Posted - 2010-09-16 : 15:22:51

Thanks everyone for your help. It worked and I have the results I was looking for.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources