NZ function in T-SQL where ValueIfNull is NOT specified

I am working on an MS Access to SQL Server Migration project and am currently in the process of converting a complex query into T-SQL from MS Access. Now I’m quite familiar with how Nz works in Access and how a 0 or empty string ” “ is returned if the valueifnull argument is not provided. Source: https://support.office.com/en-gb/article/nz-function-8ef85549-cc9c-438b-860a-7fd9f4c69b6c

The exception to this rule is if the Nz function is used within a query expression, in which case, the returned value in the event of a null in the Variant, is an empty string.

Now, moving onto my actual problem, I am working on converting this Nz-filled query expression into T-SQL using ISNULL in T-SQL. ISNULL requires 2 arguments. Both the Expression and the Value. The latter being an optional argument in Access’ Nz, has made it a bit difficult for me to translate the expression. The Value argument also needs to match the original data type of the Variant (in SQL Server), this means that I can not just simply add an empty string ” “ as the second argument as Nz in Access does by default.

Take a snippet of the complex query I use in Access compared to what I’ve written in T-SQL:

Access

TotalWIP: IIf([PercentageDoneTotal]<0,0,IIf(nz([TotalPurchasesReceived])+ 
(IIf([PercentageDoneTotal]>0,nz([TotalStockAllocated]),0))+IIf((([Accepted 
Price]*[OutstandingBalance]*0.9)-nz([TotalPurchasesReceived])- 
(IIf([PercentageDoneTotal]>0,nz([TotalStockAllocated]),0)))>0,((([Accepted 
Price]*[OutstandingBalance]*0.9)-nz([TotalPurchasesReceived])- 
(IIf([PercentageDoneTotal]>0,nz([TotalStockAllocated]),0)))* 
[PercentageDoneTotal]),0)>([Accepted Price]*[OutstandingBalance]*0.9), 
([Accepted Price]*[OutstandingBalance]*0.9),nz([TotalPurchasesReceived])+ 
(IIf([PercentageDoneTotal]>0,nz([TotalStockAllocated]),0))+IIf((([Accepted 
Price]*[OutstandingBalance]*0.9)-nz([TotalPurchasesReceived])- 
(IIf([PercentageDoneTotal]>0,nz([TotalStockAllocated]),0)))>0,((([Accepted 
Price]*[OutstandingBalance]*0.9)-nz([TotalPurchasesReceived])- 
(IIf([PercentageDoneTotal]>0,nz([TotalStockAllocated]),0)))* 
[PercentageDoneTotal]),0)))

T-SQL

IIf([PercentageDoneTotal]<0,0,
IIf(ISNULL([TotalPurchasesReceived],NULL)+ 
(IIf([PercentageDoneTotal]>0,ISNULL([TotalStockAllocated],NULL),0))+
IIf((([Accepted Price]*[OutstandingBalance]*0.9)- 
ISNULL([TotalPurchasesReceived],NULL)
-(IIf([PercentageDoneTotal]>0,ISNULL([TotalStockAllocated],NULL),0)))>0, 
((([Accepted Price]*[OutstandingBalance]*0.9)- 
ISNULL([TotalPurchasesReceived],NULL)- 
(IIf([PercentageDoneTotal]>0,ISNULL([TotalStockAllocated],NULL),0)))* 
[PercentageDoneTotal]),0)>([Accepted Price]*[OutstandingBalance]*0.9), 
([Accepted Price]* 
[OutstandingBalance]*0.9),ISNULL([TotalPurchasesReceived],NULL)+ 
(IIf([PercentageDoneTotal]>0,ISNULL([TotalStockAllocated],NULL),0))+
 IIf((([Accepted Price]*[OutstandingBalance]*0.9)- 
 ISNULL([TotalPurchasesReceived],NULL)- 
(IIf([PercentageDoneTotal]>0,ISNULL([TotalStockAllocated],NULL),0)))>0, 
((([Accepted Price]*[OutstandingBalance]*0.9)- 
ISNULL([TotalPurchasesReceived],NULL)- 
(IIf([PercentageDoneTotal]>0,ISNULL([TotalStockAllocated],NULL),0)))* 
[PercentageDoneTotal]),0))
 ) AS TotalWIP

Note the above NULL in the T-SQL example. This NULL is not suitable as it is messing up my calculations, I essentially need to mimic Nz without a second argument in T-SQL.

Edit: Without putting the entire query into this question, Here is an expected result for Job Number: 294784 in both MS Access and SQL Server:

Access Results:

SQL Results:

Edit 2: have added the edited to add entire code.

Answer

As @Salman A noted, this is a great reason not to use variant. But having said that, I assume that you are looking for a numeric value, so why not use:

    IIF([PercentageDoneTotal]<0,0, IIF(ISNULL([TotalPurchasesReceived],0)+ 
      (IIF([PercentageDoneTotal]>0,ISNULL([TotalStockAllocate],0),0))

Advertisement

Answer