amaach amaach - 6 months ago 8
SQL Question

How to add condition based on if statement

I am writing this stored procedure where I want to add a third AND condition based on IF statement, I am having errors with the IF statement. Is this the correct syntax to do this in Oracle? thanks

FROM U_DEBT D,
U_DOCUMENT_DEBT UDD,

where D.DEBT_TYPE='I' AND UDD.DEBT_NUM=D.DEBT_NUM
AND UDD.FS_STATEMENT_NUM=UUI.FS_STATEMENT_NUM

if P5_LAST_NAME is not null then
AND D.FORMATTED_LAST_NAME IN (SELECT local.pkg_bs_common.web_format(P5_LAST_NAME) FROM DUAL);
end if;

Answer

You can do it like this:

WHERE D.DEBT_TYPE='I' AND 
      UDD.DEBT_NUM=D.DEBT_NUM AND 
      UDD.FS_STATEMENT_NUM=UUI.FS_STATEMENT_NUM AND 
      (P5_LAST_NAME IS NULL OR 
       D.FORMATTED_LAST_NAME IN (SELECT local.pkg_bs_common.web_format(P5_LAST_NAME) 
                                 FROM DUAL)