Remove if else and use CASE instead

[ronanBas]

Friends
How can we use CASE statements in the following code
28:
IF (V_SOURCE_CODE = 'DEPOSIT') THEN

DELETE FROM INTERIM_AUTO_CASHFLOW AC
WHERE EXISTS
(SELECT *
FROM INTERIM_AUTO_CASHFLOW AC,
pl_mu_con_mat_conf AF,
Control_Matrix_Conf MC
WHERE AF.control_matrix_conf_id =
MC.control_matrix_conf_id
AND AC.MU_ID = AF.MU_ID(+)
AND AC.PRODUCT_LINE_ID = AF.PRODUCT_LINE_ID
AND MC.control_matrix_conf_code = 'AA')

ELSE

DELETE FROM INTERIM_AUTO_CASHFLOW AC
WHERE EXISTS
(SELECT *
FROM INTERIM_AUTO_CASHFLOW AC,
pl_mu_con_mat_conf AF,
Control_Matrix_Conf MC
WHERE AF.control_matrix_conf_id =
MC.control_matrix_conf_id
AND AC.MU_ID = AF.TBL_GLRS_MU_ID(+)
AND AC.PRODUCT_LINE_ID = AF.PRODUCT_LINE_ID
AND MC.control_matrix_conf_code IN ('AC', 'AN'));
END IF;

 

[SantaMufasa]

Ronan,

First, welcome to Tek-Tips and to the Oracle forums.

Now, to your request...Remember that a CASE statement results in data based upon conditions; an IF statement results in processing based upon conditions.

When you say, "How can we use CASE statements in the following code," do you want to know how to embed a CASE statement within the IF statement, or do you want to know how to replace an IF statement with a CASE statement? If the latter, then it is functionally not appropriate to try to replace an IF with a CASE.

Let us know if this addresses your issue. If it does not, then please clarify your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[ronanBas]

Thanks
my question is how to embed a CASE statement within the IF statement?

 

[Dagon]

I'm not sure why you would specifically want a CASE statement rather than another IF, but it's certainly possible to do it e.g.

begin
    if 1+1 = 2 then
      case when 2+2=4 then
         dbms_Output.put_line('1+1=2 and 2+2=4');
      else
         dbms_Output.put_line('The world of numbers is falling apart');
      end case;
    end if;
end;

You can also of course you CASE statements in SQL but not IF statements.

 

[ronanBas]

Thanks all
I had to remove the CASE and MERGE the DELETE as below

DELETE FROM INTERIM_AUTO_CASHFLOW AC
WHERE EXISTS
(SELECT *
FROM INTERIM_AUTO_CASHFLOW AC,
pl_mu_con_mat_conf AF,
Control_Matrix_Conf MC
WHERE AF.control_matrix_conf_id = MC.control_matrix_conf_id
AND AC.MU_ID = AF.TBL_GLRS_MU_ID(+)
AND AC.PRODUCT_LINE_ID = AF.PRODUCT_LINE_ID
AND ((V_SOURCE_CODE = 'DEPOSIT' AND MC.control_matrix_conf_code = 'AA') OR MC.control_matrix_conf_code IN ('AC', 'AN')));