I have a query with the following SQL statement:

SELECT qryTotalRec.ProjectID, qryTotalRec.ManPartNoID, qryTotalRec.ManPartNumber, qryTotalRec.SumOfQuantity, qryTotalRem.SumOfQtyInvRemove, IIf((Nz([SumOfQtyInvRemove],0))=0,[SumOfQuantity],[SumOFQuantity]-[SumOfQtyInvRemove]) AS TotalInv, qryTotalRem.ManPNInvRemove
FROM qryTotalRem RIGHT JOIN qryTotalRec ON qryTotalRem.ManPNInvRemove = qryTotalRec.ManPartNoID
GROUP BY qryTotalRec.ProjectID, qryTotalRec.ManPartNoID, qryTotalRec.ManPartNumber, qryTotalRec.SumOfQuantity, qryTotalRem.SumOfQtyInvRemove, IIf((Nz([SumOfQtyInvRemove],0))=0,[SumOfQuantity],[SumOFQuantity]-[SumOfQtyInvRemove]), qryTotalRem.ManPNInvRemove;

I am trying to get this query into our SQL 2005 server and I need to rewrite it to convert the IIF to a case and the NZ to an IsNull. I have tried to look up how to do this, but I am so unfamiliar with SQL that it is like reading German. If someone could help me rewrite this part:

IIf((Nz([SumOfQtyInvRemove],0))=0,[SumOfQuantity],[SumOFQuantity]-[SumOfQtyInvRemove])

to make it compatible with an SQL query, I would be eternally grateful.

I'm not the most familiar with SQL for SQL Server as I mostly use SQL for iSeries but I think the use of Case is the same.

CASE WHEN IsNull([SumOfQtyInvRemove]) THEN [SumOfQuantity] ELSE [SumOfQuantity]-[SumOfQtyInvRemove] END

When your expression isn't a Boolean value you can write it as:

CASE expression WHEN something THEN something ELSE something END.

you can also have more than one WHEN - THEN as well I believe.

Hope this helps!

OK, this is what I put in:

SELECT qryTotalRec.ProjectID, qryTotalRec.ManPartNoID, qryTotalRec.ManPartNumber, qryTotalRec.SumOfQuantity, qryTotalRem.SumOfQtyInvRemove,
CASE WHEN IsNull([SumOfQtyInvRemove],0) THEN [SumOfQuantity] ELSE [SumOfQuantity]-[SumOfQtyInvRemove] END,
AS TotalInv, qryTotalRem.ManPNInvRemove
FROM qryTotalRem RIGHT JOIN qryTotalRec ON qryTotalRem.ManPNInvRemove = qryTotalRec.ManPartNoID
GROUP BY qryTotalRec.ProjectID, qryTotalRec.ManPartNoID, qryTotalRec.ManPartNumber, qryTotalRec.SumOfQuantity, qryTotalRem.SumOfQtyInvRemove,
CASE WHEN IsNull([SumOfQtyInvRemove],0) THEN [SumOfQuantity] ELSE [SumOfQuantity]-[SumOfQtyInvRemove] END, qryTotalRem.ManPNInvRemove;

This is the error it gave me:

Msg 4145, Level 15, State 1, Line 2
An expression of non-boolean type specified in a context where a condition is expected, near 'THEN'.

What do you think?

Another Update. I seem to have fixed the previous error by doing this:


SELECT qryTotalRec.ProjectID, qryTotalRec.ManPartNoID, qryTotalRec.ManPartNumber, qryTotalRec.SumOfQuantity, qryTotalRem.SumOfQtyInvRemove,
CASE WHEN (IsNull([SumOfQtyInvRemove],0)=0) THEN [SumOfQuantity] ELSE [SumOfQuantity]-[SumOfQtyInvRemove] END
AS TotalInv, qryTotalRem.ManPNInvRemove
FROM qryTotalRem RIGHT JOIN qryTotalRec ON qryTotalRem.ManPNInvRemove = qryTotalRec.ManPartNoID
GROUP BY qryTotalRec.ProjectID, qryTotalRec.ManPartNoID, qryTotalRec.ManPartNumber, qryTotalRec.SumOfQuantity, qryTotalRem.SumOfQtyInvRemove,
CASE WHEN (IsNull([SumOfQtyInvRemove],0)=0) THEN [SumOfQuantity] ELSE [SumOfQuantity]-[SumOfQtyInvRemove] END, qryTotalRem.ManPNInvRemove;

But now it is not reconciliation the other queries. This sounds like more of a "I don't understand SQL Server" issue than anything. If you could point me in the right direction I would appreciate it. It is is giving me this issue:


Msg 208, Level 16, State 1, Line 1
Invalid object name 'qryTotalRem'.

I saved bot qryTotalRec and qryTotalRem to the Projects Folder as a 'SQL Files' type.

Thanks for the help.