Access to SQL

[bruce55]

I am trying to convert a Access Qry to SQL
could anyone help?

SELECT QryAdjCostEXVat.OrderNo, Sum(IIf([AdjInd]=2,-[ExVat],[ExVat])) AS Val
FROM QryAdjCostEXVat
GROUP BY QryAdjCostEXVat.OrderNo
HAVING (((Sum(IIf([AdjInd]=2,-[ExVat],[ExVat])))<>0))

 

[Tarwn]

The only major thing that won't work is the IIF() function. You can replace that functionality with a CASE statement in T-SQL (If you are indeed switching to SQL Server).

IIf([AdjInd]=2,-[ExVat],[ExVat])

would be replaced with something like

CASE WHEN [AdjInd]=2 THEN -[ExVat] ELSE [ExVat] END
or
CASE [AdjInd] WHEN 2 THEN -[ExVat] ELSE [ExVat] END

It make look wordier (ok, it is), but CASE statements are similar to Select Case statements in ASP, you can chain several condition blocks together (WHEN's in this case) and you can either use it like a Select Case from VBScript (2nd example) or put a differant conditional statement in each WHEN (1st example).

-T

 

[bruce55]

i managed to get the case bit in it was the

HAVING (((Sum(IIf([AdjInd]=2,-[ExVat],[ExVat])))<>0))
i couldnt word right ? i wasnt sure about the brackets