Testing for Null - 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

Testing for Null

Author

Topic

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-10-14 : 10:13:58

In the query below, I need to perform the Case When on a field in the poitem_ext table. I want something like (pseudocode):

Case when poitem_ext.salesorderno is null then
sum (poitem.fucost * (poitem.fordqty - poitem.frcpqty))
else
0

How can I write this?

SELECT poitem.fpartno,
       poitem.frev,
			 (case when 
       sum (poitem.fucost * (poitem.fordqty - poitem.frcpqty))
			 
			 			 
			 
			 
			 
  FROM    M2MDATA01.dbo.inmast inmast
       INNER JOIN
          (   M2MDATA01.dbo.pomast pomast
           INNER JOIN
              M2MDATA01.dbo.poitem poitem
           ON pomast.fpono = poitem.fpono
					 left outer join M2MDATA01.dbo.poitem_ext poitem_ext on poitem.identity_column = poitem_ext.Fkey_ID)
       ON ( (inmast.fpartno = poitem.fpartno) AND (inmast.frev = poitem.frev))
      AND (inmast.fac = poitem.Fac)
 WHERE (poitem.frelsno <> '  0' OR poitem.fmultirls <> 'Y')
   AND pomast.fstatus = 'OPEN'
   AND poitem.FLSTPDATE <= '01/01/2009 12:00:00 AM'
GROUP BY poitem.fpartno, poitem.frev

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-14 : 10:30:29

yup you got it.

Case when poitem_ext.salesorderno is null then
sum (poitem.fucost * (poitem.fordqty - poitem.frcpqty))
else
0 end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources