I have a SQL with nvl and case statement that has to be converted into Informatica expression

Question

I have the following SQL statement:

nvl(W_SALES_INVOICE_LINE_FS.INVOICED_QTY,
case nvl(W_SALES_INVOICE_LINE_FS.NET_AMT,0) when 0 then W_SALES_INVOICE_LINE_FS.INVOICED_QTY
else -1 end)

Could someone help me converting it into an Informatica expression with ISNULL and DECODE(). I tried the following and it gives out Parsing errors:

IIF(ISNULL(EXT_INVOICED_QTY), 
DECODE(EXT_NET_AMOUNT, IIF(ISNULL(EXT_NET_AMOUNT),0,EXT_NET_AMOUNT) =0, EXT_INVOICED_QTY, -1),
EXT_INVOICED_QTY)

To note:

W_SALES_INVOICE_LINE_FS.INVOICED_QTY = EXT_INVOICED_QTY W_SALES_INVOICE_LINE_FS.NET_AMT = EXT_NET_AMOUNT

Answer 1

Your SQL statement is a little bit strange. Let's analyse it:

  W_SALES_INVOICE_LINE_FS.INVOICED_QTY != NULL =>  W_SALES_INVOICE_LINE_FS.INVOICED_QTY
  W_SALES_INVOICE_LINE_FS.INVOICED_QTY = NULL AND W_SALES_INVOICE_LINE_FS.NET_AMT = NULL => W_SALES_INVOICE_LINE_FS.INVOICED_QTY (but it's NULL)
  W_SALES_INVOICE_LINE_FS.INVOICED_QTY = NULL  AND W_SALES_INVOICE_LINE_FS.NET_AMT != NULL = > -1

It seems that you just need to have exactly the same output:

  DECODE ( TRUE,
    NOT isNULL( EXT_INVOICED_QTY ),  EXT_INVOICED_QTY , 
     isNULL(EXT_NET_AMT), NULL,
     -1)