Cannot use Select within DECODE Stmnt?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Cannot use Select within DECODE Stmnt?

  1. #1
    Join Date
    Aug 2000
    Posts
    68

    Cannot use Select within DECODE Stmnt?

    Hello all


    Getting a complile error, when i try and use the following decode statement within a cursor for loop.

    I cant compile as the pkg does not like the select statement within the decode statement,

    Is the decode statement restricting the ability to perform a select statement within it?

    Anybody else encounter this problem or know of a way round in when using the decode statement?

    Thanks in advance



    This is the simplified code:

    For C_cursor in r_curosr

    INSERT INTO SALES_LEDGER (
    LAST_UPDATE_DATE, /
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN,
    TRANSACTION_ID,
    GROUP_ID,
    SPREAD_FLAG,
    TRANSACTION_REASON,
    DERIVED_PROCESS_FLAG,
    quantity,
    total_value_order,
    reporting_date

    )
    VALUES (

    SYSDATE,
    SYSDATE
    g_user_id,
    SYSDATE,
    g_user_id,
    g_login_id,
    LEDGER_S1.NEXTVAL,
    l_group_id,
    r_cursor.SPREAD_FLAG,
    'Scheduled Change',
    'N',
    l_sum_qty,
    l_total_val,
    decode(l_date_flag, 'Y',last_day(add_months(sysdate, -1)),'E',
    (select date_requested_current from
    so_headers_all where header_id= r_cursor.HEADER_ID),sysdate)

    );
    END Loop;


    This is the error, i cant compile as the pkg does not like the select statement within the decode statement

    (1):PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
    (1):
    (1): ( - + mod not null others
    (1): avg
    (1): count current exists max min prior sql stddev sum variance
    (1): execute forall time timestamp interval date
    (1):

    (1):

    (2):PLS-00103: Encountered the symbol "," when expecting one of the following:
    (2):
    (2): ; return returning and or
    Carpe Diem

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    1) you need to reference C_cursor in the loop (not r_cursor) - typo?

    2) since it's PL/SQL, I'd do a "SELECT INTO myvar" before the INSERT and use myvar in the DECODE
    otherwise try:
    select decode(l_date_flag, 'Y', last_day(add_months(sysdate, -1)),'E', requested_current, sysdate)
    from so_headers_all where header_id= r_cursor.HEADER_ID

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,003
    In addition to what DaPi said, the values don't lione up with the columns. For every column there is one value to go with it. Not to mention that there were numerous extra characters as well as a missing colum. This is my interpretation of what you were trying to do.

    Code:
    SELECT date_requested_current
      INTO v_my_date
      FROM so_headers_all 
     WHERE header_id = r_cursor.header_id;
    
    INSERT INTO sales_ledger
       ( last_update_date,
         last_updated_by,
         last_update_login,
         transaction_id,
         GROUP_ID,
         spread_flag,
         transaction_reason,
         derived_process_flag,
         quantity,
         total_value_order,
         reporting_date )
    VALUES ( SYSDATE,
         g_user_id,
         SYSDATE,
         g_user_id,
         ledger_s1.NEXTVAL,
         l_group_id,
         r_cursor.spread_flag,
         'Scheduled Change',
         l_sum_qty,
         l_total_val,
         v_my_date );
    this space intentionally left blank

  4. #4
    Join Date
    Aug 2000
    Posts
    68

    thanks

    Thanks for your input.

    Apologies for the typos, i relaize that they did'nt help my plight!

    I used the select date requested current into a variable, and used the variable in my decode statement.

    I could'nt get it working using the select in the decode statement.
    Carpe Diem

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,003

    Re: thanks

    Originally posted by Barabas
    Thanks for your input.

    Apologies for the typos, i relaize that they did'nt help my plight!

    I used the select date requested current into a variable, and used the variable in my decode statement.

    I could'nt get it working using the select in the decode statement.
    If you are getting an error now is the time to post it.
    this space intentionally left blank

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

    Re: thanks

    Originally posted by Barabas
    I could'nt get it working using the select in the decode statement.
    There are limitations on where you can put a select statement - these limitations are version dependant and in some versions they are different between SQL & PL/SQL. You need to RT*M(s) to find out exactly what you can and can't do.

    If you insist on it being a single SQL statement, you will need to recode it something like:
    INSERT INTO sales_ledger(. . . .)
    (SELECT va1, val2, . . . , decode(...) . . . FROM so_headers_all WHERE . . .)

    But I still think it will be more readable if you separate the select & decode from the insert.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width