How to incorporate conditions in where clause using CASE statements in cursor

How to incorporate conditions in where clause using CASE statements in the cursor.I have defined V_CATEGORY=11 in the declare statements. so based on the defined category  it has to take the where conditions.

DECLARE
V_NODE_ID  VARCHAR2(20);
V_CATEGORY VARCHAR2(20) :=11;

CURSOR C1 IS
SELECT isbn FROM GT_ADD_ISBNS GT;


CURSOR C2(v_isbn in varchar2) IS
SELECT ANP.NODE_ID NODE_ID
            FROM
            TABLE1 ANP,
            TABLE2 ANPP,
            TABLE3 AN
            WHERE
      ANP.NODE_ID=AN.ID AND
   
      
      CASE WHEN V_CATEGORY =11 THEN
    ANP.STRING_VALUE ='pdf' AND
    ANPP.STRING_VALUE ='product' AND
      
      CASE WHEN V_CATEGORY =10 THEN
    ANP.STRING_VALUE ='IMAGE' AND
    ANPP.STRING_VALUE ='product11' AND
      END CASE
      
    ANP.TAME_ID=21 AND
             ANP.NODE_ID=v_isbn;


BEGIN
--Iterating all Products
FOR R1 IN C1
LOOP


BEGIN


--To check whether Product is exists or not
SELECT AN.ID INTO V_NODE_ID
FROM TABLE1 AN,
TABLE2 ANP
WHERE
AN.ID=ANP.NODE_ID AND
ANP.STRING_VALUE in(R1.ISBN);




FOR R2 IN C2(R1.ISBN)
LOOP


 


END LOOP;

 
 
 
EXCEPTION WHEN
NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('PRODUCT NOT FOUND.....'|| R1.ISBN);
END;

END LOOP;--looping gt_isbns
END;
chaituu chaituAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
easiest way DON'T use CASE

try declaring your cursor like this...

    CURSOR c2(
        v_isbn   IN VARCHAR2)
    IS
        SELECT anp.node_id node_id
          FROM table1 anp, table2 anpp, table3 an
         WHERE anp.node_id = an.id
           AND ((v_category = 11 AND anp.string_value = 'pdf' AND anpp.string_value = 'product')
             OR (v_category = 10 AND anp.string_value = 'IMAGE' AND anpp.string_value = 'product11'))
           AND anp.tame_id = 21
           AND anp.node_id = v_isbn;


if you're interested in an academic sense (and not actually using this in production code) then your CASE might look something like this...

    CURSOR c2(
        v_isbn   IN VARCHAR2)
    IS
        SELECT anp.node_id node_id
          FROM table1 anp, table2 anpp, table3 an
         WHERE anp.node_id = an.id
           AND CASE
                   WHEN v_category = 11 AND anp.string_value = 'pdf' AND anpp.string_value = 'product' THEN 1
                   WHEN v_category = 10 AND anp.string_value = 'IMAGE' AND anpp.string_value = 'product11' THEN 2
               END IN
                   (1, 2)
           AND anp.tame_id = 21
           AND anp.node_id = v_isbn;

Alternately, you could assign both THEN conditions the same value and do an equality


    CURSOR c2(
        v_isbn   IN VARCHAR2)
    IS
        SELECT anp.node_id node_id
          FROM table1 anp, table2 anpp, table3 an
         WHERE anp.node_id = an.id
           AND CASE
                   WHEN v_category = 11 AND anp.string_value = 'pdf' AND anpp.string_value = 'product' THEN 1
                   WHEN v_category = 10 AND anp.string_value = 'IMAGE' AND anpp.string_value = 'product11' THEN 1
               END  =  1
           AND anp.tame_id = 21
           AND anp.node_id = v_isbn;


Again though, neither of these case versions are good implementations.  Use the nested AND/OR conditions in the first variation above.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Geert GOracle dbaCommented:
it's probably better to create multiple cursors
1 for each scenario, especially if you don't have a lot of choices

a case can be better in some cases for performance
the optimizer tends to evaluate an (and + or) and can take time to come up with an execution plan
the case can shortcut the optimizers calculations
0
chaituu chaituAuthor Commented:
Thanks for the detailed explaination. Lot to learn from you.thanks a lot.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.