Hi All,
I want to use the CASE statement in the WHERE clause to build the condition for a column with different values, I tried as below but getting the error as 'PL/SQL : ORA-00905 : missing keyword' on compiling the stored procedure.
I have the stored procedure which includes the statements like below,
Query 1: gets the column values into the variables
SELECT col1, col2 INTO v_col1,v_col2 FROM tab1 WHERE col3 = 'TEST';
Query 2: based on the values in the variables the colums in the tab2 should be assigned accordingly,
SELECT SUM(tab2.qty) INTO v_tot_qty
FROM tab2
WHERE tab2.col1 = v_col2
AND CASE WHEN v_col1 = 'NONE' THEN tab2.col2 IS NULL ELSE tab2.col2 = v_col1 END;
I am looking for the final query to be executed something like.
SELECT SUM(tab2.qty) INTO v_tot_qty
FROM tab2
WHERE tab2.col1 = v_col2
AND tab2.col2 IS NULL;
-- when the v_col1 = 'NONE'
else the query should be
SELECT SUM(tab2.qty) INTO v_tot_qty
FROM tab2
WHERE tab2.col1 = v_col2
AND tab2.col2 =v_col1;
Please suggest.
Thanks in advance!
~Shiv
;