1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Need help with a cascading case when clause inside a merge inside a stored procedure!

Discussion in 'SQL PL/SQL' started by PapaGeek, Mar 13, 2013.

  1. PapaGeek

    PapaGeek Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    I had a DBA friend write this for me, I can't go back to him for a fix, and I probably explained the issue wrong.

    It took a while to get the code to compile, and it now throws an error on the second case when clause.

    The code should return the exexflag as:

    0 whenever there is a failure at the DB level (should never happen)
    1 when the entry is a new entry in the database
    2 when the entry already existed in the database and the price was the same
    3 when the entry already existed in the database and the price was changed

    Cant find any examples on line of how to write a "case when ( ... is ... AND ... is ...) then" that cascades into multiple entries under one when clasue to define execflag as a single value. Hope I said the right!


    Code (Text):
    CREATE OR REPLACE PACKAGE BODY COMPANY.PRICE_DAILY_MAINTENANCE
    AS
    PROCEDURE PROCESS_PRICE(
      pPriceDate  IN   PRICE_DAILY.PRICE_DATE%TYPE ,
      pVendor     IN   PRICE_DAILY.VENDOR%TYPE ,
      pItem       IN   PRICE_DAILY.ITEM%TYPE ,
      pPrice      IN   PRICE_DAILY.PRICE%TYPE ,
      pCursor     OUT  SYS_REFCURSOR)
      AS
        execflag number;
        prevprice number;
        rcount   number;
      BEGIN
     
      OPEN pCursor FOR
     
        With Vars As (
            SELECT  pPriceDate   as price_date,
                    pVendor      as vendor,
                    pItem        as item,
                    pPrice       as price
            FROM dual
        )
        SELECT  --prevprice: 0 when price is inserted, the old price when updated
                case when sd.price is null then 0 else sd.price end,
                --execflag: 2 when price is the same, 1 when inserted, 3 when updated
                case when sd.price is null then 1 when sd.price = vars.price then 2 else 3 end
                into prevprice, execflag
    from vars
    left join COMPANY.price_daily sd
        on sd.vendor = vars.vendor
        and sd.item = vars.item
        and sd.price_date = vars.price_date
    ;
    MERGE INTO COMPANY.price_daily sd
    USING (
            SELECT  pPriceDate   as price_date,
                    pVendor      as vendor,
                    pItem        as item,
                    pPrice       as price

    FROM dual
    ) vars
    ON (sd.vendor = vars.vendor
        AND sd.item = vars.item
        AND sd.price_date = vars.price_date
    )
    WHEN MATCHED THEN
        UPDATE SET price = vars.price
    WHEN NOT MATCHED THEN
    insert (price_date, vendor, item, price)
          values (pPriceDate, pVendor, pItem, pPrice)
    ;
      END PROCESS_PRICE;
     
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: Need help with a cascading case when clause inside a merge inside a stored proced

    Not really but I just posted an example of a compound CASE when clause and I'll post it here:

    Code (SQL):
    SQL> CREATE TABLE stud_course(
      2          stud_id NUMBER,
      3          subject varchar2(20),
      4          lvl     varchar2(4),
      5          instructor varchar2(40)
      6  );
     
    TABLE created.
     
    SQL>
    SQL> BEGIN
      2          FOR i IN 1..100 loop
      3                  IF MOD(i, 17) = 0 THEN
      4                          INSERT INTO stud_course
      5                          VALUES(i, 'Math', 'CP', 'Fnarm');
      6                  elsif MOD(i, 33) = 0 THEN
      7                          INSERT INTO stud_course
      8                          VALUES(i, 'Math', 'XP', 'Fnorm');
      9                  elsif MOD(i, 77) = 0 THEN
     10                          INSERT INTO stud_course
     11                          VALUES(i, 'English', 'CP', 'Fnurm');
     12                  ELSE
     13                          INSERT INTO stud_course
     14                          VALUES(i, 'Chemistry','CP','Yomper');
     15                  END IF;
     16
     17          END loop;
     18
     19          commit;
     20
     21  END;
     22  /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> SELECT stud_id,
      2         lvl, subject,
      3          CASE WHEN lvl = 'CP' AND subject = 'Math' THEN 'Y' ELSE 'N' END MathCk,
      4          instructor
      5  FROM stud_course
      6  ORDER BY 4;
     
       STUD_ID LVL  SUBJECT              M INSTRUCTOR
    ---------- ---- -------------------- - ----------------------------------------
             1 CP   Chemistry            N Yomper
             2 CP   Chemistry            N Yomper
             3 CP   Chemistry            N Yomper
             4 CP   Chemistry            N Yomper
             5 CP   Chemistry            N Yomper
             6 CP   Chemistry            N Yomper
             7 CP   Chemistry            N Yomper
             8 CP   Chemistry            N Yomper
             9 CP   Chemistry            N Yomper
            10 CP   Chemistry            N Yomper
            11 CP   Chemistry            N Yomper
     
       STUD_ID LVL  SUBJECT              M INSTRUCTOR
    ---------- ---- -------------------- - ----------------------------------------
            12 CP   Chemistry            N Yomper
            13 CP   Chemistry            N Yomper
            14 CP   Chemistry            N Yomper
            15 CP   Chemistry            N Yomper
            16 CP   Chemistry            N Yomper
            18 CP   Chemistry            N Yomper
            19 CP   Chemistry            N Yomper
            20 CP   Chemistry            N Yomper
            21 CP   Chemistry            N Yomper
            22 CP   Chemistry            N Yomper
            23 CP   Chemistry            N Yomper
     
       STUD_ID LVL  SUBJECT              M INSTRUCTOR
    ---------- ---- -------------------- - ----------------------------------------
            24 CP   Chemistry            N Yomper
            25 CP   Chemistry            N Yomper
            26 CP   Chemistry            N Yomper
            27 CP   Chemistry            N Yomper
            28 CP   Chemistry            N Yomper
            29 CP   Chemistry            N Yomper
            30 CP   Chemistry            N Yomper
            31 CP   Chemistry            N Yomper
            32 CP   Chemistry            N Yomper
            33 XP   Math                 N Fnorm
            35 CP   Chemistry            N Yomper
     
       STUD_ID LVL  SUBJECT              M INSTRUCTOR
    ---------- ---- -------------------- - ----------------------------------------
            36 CP   Chemistry            N Yomper
            37 CP   Chemistry            N Yomper
            38 CP   Chemistry            N Yomper
            39 CP   Chemistry            N Yomper
            40 CP   Chemistry            N Yomper
            41 CP   Chemistry            N Yomper
            42 CP   Chemistry            N Yomper
            43 CP   Chemistry            N Yomper
            44 CP   Chemistry            N Yomper
            45 CP   Chemistry            N Yomper
            46 CP   Chemistry            N Yomper
     
       STUD_ID LVL  SUBJECT              M INSTRUCTOR
    ---------- ---- -------------------- - ----------------------------------------
            47 CP   Chemistry            N Yomper
            48 CP   Chemistry            N Yomper
            49 CP   Chemistry            N Yomper
            50 CP   Chemistry            N Yomper
            52 CP   Chemistry            N Yomper
            53 CP   Chemistry            N Yomper
            54 CP   Chemistry            N Yomper
            55 CP   Chemistry            N Yomper
            56 CP   Chemistry            N Yomper
            57 CP   Chemistry            N Yomper
            58 CP   Chemistry            N Yomper
     
       STUD_ID LVL  SUBJECT              M INSTRUCTOR
    ---------- ---- -------------------- - ----------------------------------------
            59 CP   Chemistry            N Yomper
            60 CP   Chemistry            N Yomper
            61 CP   Chemistry            N Yomper
            62 CP   Chemistry            N Yomper
            63 CP   Chemistry            N Yomper
            64 CP   Chemistry            N Yomper
            65 CP   Chemistry            N Yomper
            66 XP   Math                 N Fnorm
            67 CP   Chemistry            N Yomper
            69 CP   Chemistry            N Yomper
            70 CP   Chemistry            N Yomper
     
       STUD_ID LVL  SUBJECT              M INSTRUCTOR
    ---------- ---- -------------------- - ----------------------------------------
            71 CP   Chemistry            N Yomper
            72 CP   Chemistry            N Yomper
            73 CP   Chemistry            N Yomper
            74 CP   Chemistry            N Yomper
            75 CP   Chemistry            N Yomper
            76 CP   Chemistry            N Yomper
            77 CP   English              N Fnurm
            78 CP   Chemistry            N Yomper
            79 CP   Chemistry            N Yomper
            80 CP   Chemistry            N Yomper
            81 CP   Chemistry            N Yomper
     
       STUD_ID LVL  SUBJECT              M INSTRUCTOR
    ---------- ---- -------------------- - ----------------------------------------
            82 CP   Chemistry            N Yomper
            83 CP   Chemistry            N Yomper
            84 CP   Chemistry            N Yomper
            86 CP   Chemistry            N Yomper
            87 CP   Chemistry            N Yomper
            88 CP   Chemistry            N Yomper
            89 CP   Chemistry            N Yomper
            90 CP   Chemistry            N Yomper
            91 CP   Chemistry            N Yomper
            92 CP   Chemistry            N Yomper
            93 CP   Chemistry            N Yomper
     
       STUD_ID LVL  SUBJECT              M INSTRUCTOR
    ---------- ---- -------------------- - ----------------------------------------
            94 CP   Chemistry            N Yomper
            95 CP   Chemistry            N Yomper
            96 CP   Chemistry            N Yomper
            97 CP   Chemistry            N Yomper
            98 CP   Chemistry            N Yomper
            99 XP   Math                 N Fnorm
           100 CP   Chemistry            N Yomper
            17 CP   Math                 Y Fnarm
            34 CP   Math                 Y Fnarm
            85 CP   Math                 Y Fnarm
            68 CP   Math                 Y Fnarm
     
       STUD_ID LVL  SUBJECT              M INSTRUCTOR
    ---------- ---- -------------------- - ----------------------------------------
            51 CP   Math                 Y Fnarm
     
    100 ROWS selected.
     
    SQL>
    I am not sure if this example helps you or not. Possibly you can describe the situation you want to create in greater detail.
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Re: Need help with a cascading case when clause inside a merge inside a stored proced

    Also you can get nice example HERE on using multiple CASE statements in case you are interested.