#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    2
    Rep Power
    0

    PL/SQL error using Case Expression


    I am receiving the error ORA-06550: line 53, column 4:
    PLS-00103: Encountered the symbol "CASE" when expecting one of the following:...

    I am using the case expression within a nested IF statement. Also, the case expression will update a table depending on the outcome of the expression. The code is pretty long so I only posted a piece of it. If you need to see the entire code please let me know. Any suggestions? Thanks.

    IF gl_location = v_store_num THEN
    IF curr_fiscal_year = p_year AND curr_fiscal_period = p_num THEN

    CASE p_num
    WHEN 1 THEN UPDATE ac_util_balance SET ly_actual_01 = a_net, ly_budget_01 = b_net, cy_actual_01 = a_net, cy_budget_01 = b_net, last_actual = a_net, last_budget = b_net, last_extract_date = trunc(sysdate) WHERE gl_location = v_store_num

    WHEN 2 THEN UPDATE ac_util_balance SET ly_actual_02 = a_net, ly_budget_02 = b_net, cy_actual_02 = a_net, cy_budget_02 = b_net, last_actual = a_net, last_budget = b_net, last_extract_date = trunc(sysdate) WHERE gl_location = v_store_num

    WHEN 3 THEN UPDATE ac_util_balance SET ly_actual_03 = a_net, ly_budget_03 = b_net, cy_actual_03 = a_net, cy_budget_03 = b_net, last_actual = a_net, last_budget = b_net, last_extract_date = trunc(sysdate) WHERE gl_location = v_store_num

    WHEN 4 THEN UPDATE ac_util_balance SET ly_actual_04 = a_net, ly_budget_04 = b_net, cy_actual_04 = a_net, cy_budget_04 = b_net, last_actual = a_net, last_budget = b_net, last_extract_date = trunc(sysdate) WHERE gl_location = v_store_num

    WHEN 5 THEN UPDATE ac_util_balance SET ly_actual_05 = a_net, ly_budget_05 = b_net, cy_actual_05 = a_net, cy_budget_05 = b_net, last_actual = a_net, last_budget = b_net, last_extract_date = trunc(sysdate) WHERE gl_location = v_store_num

    ELSE null END CASE;
    .....
    END IF;
    END IF;
  2. #2
  3. No Profile Picture
    Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    305
    Rep Power
    11
    My guess, CASE....WHEN statement can only be used in SQL statement as a substitute of IF....ELSE
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    68
    Rep Power
    11
    Which Oracle version are you using?

    The CASE expression is relatively new in Oracle.
    It comes in two flavors and you are using the one which is available only in SQL 9i.
    The other one has a different syntax and was available in SQL 8.1.6 and maybe even earlier.

    It looks like:
    CASE
    WHEN <condition> THEN <return value>
    [ELSE <return value>]
    END

    The PL/SQL language is independent of the SQL one and I'm not sure in which version the two CASE expressions became available.

    So, please check the manuals for your Oracle version at http://tahiti.oracle.com/.

    Regards,
    Dan
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    68
    Rep Power
    11
    BTW, The CASE expression begins with the keyword CASE and ends with the keyword END. So, your CASE keyword at the end of the expression is not needed.
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    2
    Rep Power
    0

    PL?SQL error using Case Expression


    I do think the version of Oracle we are using is an early version than 9i. Can a CASE statement be used inside an UPDATE statement and an INSERT statement? If not, how about DECODE? I have both an UPDATE and an INSERT statement I am struggling with and didn't know if I had any other options besides using a very long nested IF statement. Any suggestions? Thanks.
  10. #6
  11. No Profile Picture
    Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    305
    Rep Power
    11
    You could use CASE.....WHEN expression in any DML statement.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Apr 2002
    Posts
    693
    Rep Power
    26

    Re: PL?SQL error using Case Expression


    Originally posted by corn09
    I do think the version of Oracle we are using is an early version than 9i. Can a CASE statement be used inside an UPDATE statement and an INSERT statement? If not, how about DECODE? I have both an UPDATE and an INSERT statement I am struggling with and didn't know if I had any other options besides using a very long nested IF statement. Any suggestions? Thanks.

    I know for a fact that decode can be used in INSERT/UPDATE statements and would be quite surprized if CASE cannot.

IMN logo majestic logo threadwatch logo seochat tools logo