Convertine local iif statements to Pass Thru syntax Question 1

[nguenthe]

I am a bit stuck on converting some local Access queries to real SQL as I am still learning SQL syntax. Can anyone assist? I butchered it pretty well below. Any assistance is greatly
appreciated. I am most stuck on going from Access "Iif" to SQL If.


SELECT TOP 120 w.loc,
w.itemnum,
If(Len(w.ABCRankOv) < 1 then set w.ABCRankOv = 'Z' elseif w.ABCRankOv) AS 'abc',
w.avgcost,
(SELECT a.onhandqty
FROM DOMDATA.PUB.icWhseItem a
WHERE a.itemnum = q_pt_icwhseitem_allwh1.itemnum
AND a.whse = 'RTL') AS 'rtl',
(SELECT a.onhandqty
FROM DOMDATA.PUB.icWhseItem a
WHERE a.itemnum = q_pt_icwhseitem_allwh1.itemnum
AND a.whse = 'RGA') AS 'rga'
FROM DOMDATA.PUB.icWhseItem AS w
WHERE ( ( ( w.lastphysdate ) < sysdate - 60 )
AND ( ( w.nonstock ) = 0 )
AND ( ( If(Len(w.ABCRankOv) < 1, 'Z', w.ABCRankOv) ) = 'A' ) )
AND (w.conum = '001')
and (w.whse = 'WH1')
OR ( ( ( w.lastphysdate ) < sysdate - 120 )
AND ( ( w.nonstock ) = 0 )
AND ( ( If(Len(w.ABCRankOv) < 1, 'Z', w.ABCRankOv) ) = 'B' ) )
AND (w.conum = '001')
and (w.whse = 'WH1')
OR ( ( ( w.lastphysdate ) < sysdate - 180 )
AND ( ( w.nonstock ) = 0 )
AND ( ( If(Len(w.ABCRankOv) < 1, 'Z', w.ABCRankOv) ) = 'C' ) )
AND (w.conum = '001')
and (w.whse = 'WH1')
OR ( ( ( w.onhandqty ) <> 0 )
AND ( ( w.lastphysdate ) < sysdate - 364 )
AND ( ( w.nonstock ) = -1 )
AND (w.conum = '001')
and (w.whse = 'WH1')
AND ( ( If(Len(w.ABCRankOv) < 1, 'Z', w.ABCRankOv) ) NOT IN (
'A', 'B', 'C' )
) )
ORDER BY If(Len(w.ABCRankOv) < 1, 'Z', w.ABCRankOv),
w.lastphysdate,
w.loc;

 

[PHV]

In ANSI SQL you should use CASE.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PWise]

does this query work in access???
i think that whay you mean
iif(Len(w.ABCRankOv) < 1,'z',w.ABCRankOv)as Abc

in Sql Server

Case when Len(w.ABCRankOv) <1 then 'z' else w.ABCRankOv end