Syntax error at expression level in Informatica-CodePudding

I have a critical logic which need to be implemented , but not getting how do i write the if logic at expression level in Informatica

My logic :

if (empid =='SBCS'):
then

    empcd = v_employee_code_number

elif empcal <> depcal and empid in ('SSC','HSC'):
then
 
    empcd = (emp_sal/avgsal) * empad

elif empcal <> depcal and empid not in ('SSC','HSC'):
then
    
    empcd = emp_del

else:
    
   empcd = emp_dev

I need to write the above logic at expression level in Informatica and even SQL query ?

I have tried below logic. It is throwing syntax error in expression

iif( empid =='SBCS',v_employee_code_number,iif(empcal <> depcal and empid in ('SSC','HSC')),(emp_sal/avgsal) * empad, empcal <> depcal and empid not in ('SSC','HSC'), emp_del,emp_dev)

The above need to write in SQL but not getting how to write it

CodePudding user response：

The standard SQL case expression (which will work in Oracle too) for your logic is:

empcd := case
  when empid = 'SBCS'
    then v_employee_code_number
  when empcal != depcal and empid in ('SSC','HSC')
    then (emp_sal/avgsal) * empad
  when empcal != depcal and empid not in ('SSC','HSC')
    then emp_del
  else emp_dev
end

CodePudding user response：

As mentioned above, the == is not a valid syntax. Also in is not supported. I've rewritten your iif statement as follows (please verify!)

iif( empid ='SBCS',
    v_employee_code_number,
    iif(empcal <> depcal and (empid = 'SSC' or empid = 'HSC'),
        (emp_sal/avgsal) * empad, 
        iif(empcal <> depcal and empid <> 'SSC' and empid <> 'HSC',
        emp_del,
        emp_dev)
    )
)

But nested IIF is really hard to read, so better yet go with DECODE function (please refer the docs for details). This would look then like:

DECODE(TRUE,
    empid ='SBCS', v_employee_code_number,
    empcal <> depcal and (empid = 'SSC' or empid = 'HSC'), (emp_sal/avgsal) * empad,
    empcal <> depcal and empid <> 'SSC' and empid <> 'HSC', emp_del,
    emp_dev
)