null testing in select query - 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

null testing in select query

Author

Topic

adjones1980
Starting Member

36 Posts

Posted - 2007-05-09 : 11:39:38

I am obviously a newbie to SQL Server but have been using Access for years and I have come up against what appears to be an age old issue concerning nulls and specifically the functions to test for them etc.

Below is the Access Query I am having difficulty with...
----------------------------------------------------------
SELECT DISTINCT Max(tbl_ScheduleDate.SchDate) AS CurrentDate, ([SchDate],"mmmm yyyy") AS MonthYear, tbl_Schedule.Area, tbl_Schedule.CP, IIf(IsNull(tbl_tmp_CP.CPID),"Not Uploaded","Uploaded") AS Loaded

FROM (tbl_Schedule INNER JOIN tbl_ScheduleDate ON tbl_Schedule.MaxMinID = tbl_ScheduleDate.MaxMinID) LEFT JOIN tbl_tmp_CP ON tbl_Schedule.CP = tbl_tmp_CP.CP

GROUP BY Format([SchDate],"mmmm yyyy"), tbl_Schedule.Area, tbl_Schedule.CP, IIf(IsNull(tbl_tmp_CP.CPID),"Not Uploaded","Uploaded"), tbl_ScheduleDate.SchDate, tbl_tmp_CP.CPID;
-------------------------------------------------------------

Ignore the Format() function as I know this is a problem and have a fix.

The problem is this field...
IIf(IsNull(tbl_tmp_CP.CPID),"Not Uploaded","Uploaded") AS Loaded

I am testing to see if data is loaded into table 'tbl_tmp_CP' that is related to 'tbl_Schedule' by checking if there is a null value or not and then adding the right response string value to a column called Loaded.

Can anyone suggest a way for me to do the above?

nr
SQLTeam MVY

12543 Posts

Posted - 2007-05-09 : 11:41:53

cse when tbl_tmp_CP.CPID is null then 'Not Uploaded' else 'Uploaded' end AS Loaded

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

adjones1980
Starting Member

36 Posts

Posted - 2007-05-09 : 12:22:09

Thanks!! Exactly what I was after!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources