asked on

SQL Query Syntax Error

I have this SELECT query that seems to work fine in SQL 2014 but when I try to run it in SQL 2008 I get an error that says "Incorrect syntax near '<'
Can anyone explain why I get this error in 2008 but not in 2014?

SELECT P.fpono As PONo, P.forddate As OrderDate, P.fvendno As VendorNo, P.fcompany As VendorName, P.fbuyer As Buyer, P.fstatus As POStatus, 
RTRIM(P1.fpartno) As PartNo, P1.fdescript As PartDesc, P1.fordqty As OrderQty, P1.frcpqty As RecQty, P1.fucostonly As UnitCost, P1.forgpdate As OrigDate, P1.flstpdate As LastDate, P1.fmeasure As UoM, 
'$'+convert(varchar(10),Convert(money,(P1.fordqty) * (P1.fucostonly))) As ExtCost, '$'+convert(varchar(10),Convert(money,IIF((P1.fordqty-P1.frcpqty)*P1.fucostonly<0,0,(P1.fordqty-P1.frcpqty)*(P1.fucostonly)))) As ExtCostOpen, IIF((P1.fordqty-P1.frcpqty)<0,0,P1.fordqty-P1.frcpqty) As OpenQty
FROM dbo.pomast AS P
     INNER JOIN dbo.poitem AS P1
            ON P1.fpono = P.fpono
	 INNER JOIN dbo.apvend As A1
			ON A1.fvendno = P.fvendno
WHERE  (P.fstatus = 'OPEN') AND (A1.fcacctnum = '5010001');

Open in new window

Jim Horn

IIF was introduced in SQL 2012, so it does not exist in 2008, hence the error message. Click on the IIF link to go to the Microsoft page, click on the Other Versions dropdown, and note that it only goes down as far as 2012.

You'll have to use CASE instead of IIF.

Change this

IIF((P1.fordqty-P1.frcpqty)<0,0,P1.fordqty-P1.frcpqty) As OpenQty

Open in new window

to this

CASE WHEN P1.fordqty - P1.frcpqty < 0 THEN 0 ELSE P1.fordqty - P1.frcpqty END As OpenQty

Open in new window

If it helps (no pun intended), here's an article I wrote called SQL Server CASE Solutions that is a handy tutorial on CASE.

Lawrence Salvucci

ASKER

Thanks Jim. I appreciate the help. I'm getting another error now that says "incorrect syntax near 'As'. Am I missing something else for the "As OpenQty" syntax?

SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lawrence Salvucci

ASKER

Thank you both for your help. I will post the errors from SSMS the way you outlined, Jim. Thanks again and I'm sure I'll be posting back again. I'm still learning....