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

LVL 1
Lawrence SalvucciInformation Technology ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
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?
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Please do this now and every time in the future when you receive an error in SSMS and post it on Experts-Exchange:

1.

Click on the error in the bottom of SSMS, and watch the cursor in the query window move.  
The new cursor location is the line that cause the error.

2.

Tell us what that line is by copy-pasting it.  
Be advised that 'line 23' in SSMS will often be different the 'line 23' in an EE code block.
Works great.

Also typo correction, as I had an extra right paren
CASE WHEN P1.fordqty - P1.frcpqty < 0 THEN 0 ELSE P1.fordqty - P1.frcpqty END As OpenQty

Open in new window

0
PortletPaulfreelancerCommented:
JIm's analysis about IIF is correct. Frankly I prefer using CASE in all SQL queries as they are standards compliant.

You have 2 IIF() uses in that query, both need to be replaced by case expressions.

Plus, both those IIF()s are very similar so it is possible to place the basic expressions into an APPLY operator (alias "OA") so that the expression result can be referenced by an alias (I have simply used "x")

try this
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, OA.x * (P1.fucostonly))) AS ExtCostOpen
    , OA.x AS OpenQty
FROM dbo.pomast AS P
INNER JOIN dbo.poitem AS P1
      ON P1.fpono = P.fpono
OUTER APPLY (
      SELECT
            CASE
                  WHEN P1.fordqty - P1.frcpqty < 0 THEN 0
                  ELSE P1.fordqty - P1.frcpqty
            END
    ) AS OA (x)
INNER JOIN dbo.apvend AS A1
      ON A1.fvendno = P.fvendno
WHERE (P.fstatus = 'OPEN')
AND (A1.fcacctnum = '5010001');

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
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....
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.