MS-Access Query for T-SQL

Question

Possible duplicate:
CASE equivalent of nested IIF statement

I am trying to convert the following Access query to T-SQL, but I am having problems with the IIF statement.

Access request

SELECT qry_LAB_LOAD_Prequery2.Asset AS SITE_REF, Format([Det],"00000") AS DET_, IIf(IsNull([MT]),"0",IIf(Right([SP_Ref],2)="WZ" And IsNull([LabLoadFileSuffix_MT]) And [MT]>0,CStr([MT]) & "CT",CStr([MT]) & [LabLoadFileSuffix_MT])) AS MT_COUNT, IIf(IIf(IsNull([MT]),0,[MT])>=IIf(IsNull([IM]),0,[IM]),0, IIf(IsNull([IM]),"0",IIf(Right([SP_Ref],2)="WZ" And IsNull([LabLoadFileSuffix_IM]),CStr([IM]) & "CT",CStr([IM]) & [LabLoadFileSuffix_IM]))) AS IM_COUNT, qry_LAB_LOAD_Prequery2.[2012 Sample Point] AS SP_REF, qry_LAB_LOAD_Prequery2.Area FROM qry_LAB_LOAD_Prequery2 WHERE (((IIf(IsNull([IM]),0,[IM])+IIf(IsNull([MT]),0,[MT]))>0)); 

I tried to convert part of it. Can someone help me fix the nested IID expression as I'm not sure.

SQL Query

 SELECT qry_LAB_LOAD_Prequery2.Asset AS SITE_REF, RIGHT('00000' + CAST([Det] AS VARCHAR(5)),5) AS DET_, ----- (the nested iff statements) qry_LAB_LOAD_Prequery2.[2012 Sample Point] AS SP_REF, qry_LAB_LOAD_Prequery2.Area FROM qry_LAB_LOAD_Prequery2 where (ISNull([IM],0) + ISNULL([MT],0)) > 0 

Answer 1

To convert a nested IIF, you need a custom CASE.

Take the first one:

Reformation I get:

 IIf( IsNull([MT]), "0", IIf(Right([SP_Ref],2)="WZ" And IsNull([LabLoadFileSuffix_MT]) And [MT]>0, CStr([MT]) & "CT", CStr([MT]) & [LabLoadFileSuffix_MT] ) ) AS MT_COUNT, 

It is converted to:

 CASE WHEN [MT] IS NULL THEN "0", ELSE CASE WHEN Right([SP_Ref],2) = "WZ" AND ([LabLoadFileSuffix_MT] IS NULL AND [MT]>0 THEN CAST([MT] AS VARCHAR) + "CT" ELSE CAST([MT] AS VARCHAR) + [LabLoadFileSuffix_MT] END END AS MT_COUNT, 

This simplifies:

 CASE WHEN [MT] IS NULL THEN "0", ELSE CAST([MT] AS VARCHAR) + CASE WHEN Right([SP_Ref],2) = "WZ" AND ([LabLoadFileSuffix_MT] IS NULL AND [MT]>0 THEN "CT" ELSE [LabLoadFileSuffix_MT] END END AS MT_COUNT, 

For the second second, I reformatted and then converted it to the next

 CASE WHEN CASE WHEN [MT] IS NULL THEN 0 ELSE [MT] END >= CASE WHEN [IM] IS NULL THEN 0 ELSE [IM] END , THEN 0, ELSE CASE WHEN [IM] IS NULL THEN "0" ELSE CASE WHEN Right([SP_Ref],2) = "WZ" AND [LabLoadFileSuffix_IM] IS NULL THEN CAST([IM] AS VARCHAR) + "CT" ELSE CAST([IM] AS VARCHAR) + [LabLoadFileSuffix_IM] END END END AS IM_COUNT, 

Glance comes down to

 CASE WHEN ISNULL([MT], 0) > = ISNULL([IM], 0) THEN 0, ELSE CASE WHEN [IM] IS NULL THEN "0" ELSE CAST([IM] AS VARCHAR) + CASE WHEN Right([SP_Ref],2) = "WZ" AND [LabLoadFileSuffix_IM] IS NULL THEN "CT" ELSE [LabLoadFileSuffix_IM] END END END AS IM_COUNT, 

Which essentially boils down to two very similar expressions for both columns.

Answer 2

Try the nested case statement.

So just take the first 2 lines:

 IIf(IsNull([MT]),"0",IIf(Right([SP_Ref],2)="WZ" And IsNull([LabLoadFileSuffix_MT]) 

will become

 CASE WHEN MT IS NULL THEN 0 ELSE CASE WHEN RIGHT(SP_Ref, 2) = "WZ" AND LabLoadFileSuffix_MT IS NULL THEN ... ELSE ... END END