CASE after SELECT..... - 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

SQL Server Development (2000)

CASE after SELECT.....

Author

Topic

Dorffius
Starting Member

36 Posts

Posted - 2002-03-20 : 10:20:25

My SQL statement is working so far as it is supposed to but I am curious as to how I might be able to handle the odd error. The problem arises when there is no BSMT.SerialNo(null). The statement then just skips this record and moves on. I need it to see that the field is null and get just BSMT. items and the VPROD.PNAME anyways. I have done case statements before but mostly dealing with two distinct SELECT statements. This one I assume must take place after the select so all the information stays in one recordset.

SELECT BSMT.PCode, VPROD.PNAME, BSMT.SerialNo, BSMT.Wgt, MIN(Rawdata.ProductionDate) as MinDate, MAX(Rawdata.ProductionDate) AS MaxDate FROM BSMT INNER JOIN APP_SRV.exportDoc.dbo.VPROD VPROD ON BSMT.PCode = VPROD.PCODE INNER JOIN ScaleData.dbo.Rawdata Rawdata ON RIGHT(RTRIM (SerialNumID), 6) = BSMT.SerialNo WHERE BSMT.OrderNo = '48343' AND BSMT.Date = '03/07/02' GROUP BY BSMT.PCode, VPROD.PNAME, BSMT.SerialNo, BSMT.Wgt ORDER BY BSMT.PCode

Thanks in advance.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-03-20 : 10:39:17

the problem is to do with the INNER JOIN statement...and the null

I've seen this solved before around here....and I think it has to do with changing to a LEFT OUTER JOIN.

Search for LEFT OUTER JOIN....or try it out.

Dorffius
Starting Member

36 Posts

Posted - 2002-03-20 : 10:40:08

Thanks a lot, I'll look for that.

davidpardoe
Constraint Violating Yak Guru

324 Posts

Posted - 2002-03-20 : 11:08:24

Use the following...

SELECT 
BSMT.PCode, VPROD.PNAME, BSMT.SerialNo, BSMT.Wgt, MIN(Rawdata.ProductionDate) as MinDate, MAX(Rawdata.ProductionDate) AS MaxDate 
FROM BSMT 
INNER JOIN 
APP_SRV.exportDoc.dbo.VPROD VPROD ON BSMT.PCode = VPROD.PCODE 
LEFT OUTER JOIN ScaleData.dbo.Rawdata Rawdata ON RIGHT(RTRIM (SerialNumID), 6) = BSMT.SerialNo 
WHERE BSMT.OrderNo = '48343' AND BSMT.Date = '03/07/02' GROUP BY BSMT.PCode, VPROD.PNAME, BSMT.SerialNo, BSMT.Wgt ORDER BY BSMT.PCode

Left outer joins basically return all rows from the table on the "left" of the join (in your case from BSMT) regardless of whether it matches the table on the "right" of the join via the ON clause. Where there is no match the joined fields (ie from Rawdata) will be NULL.

Right outer joins just work in the other direction.

============
The Dabbler!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources