Decode problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Decode problem

  1. #1
    Join Date
    Jul 2000
    Location
    india
    Posts
    213

    Decode problem

    Hi All ,

    When i run this query at sql prompt it is running fine..

    SQL> SELECT A.FOS_TYPE,B.DESCRIPTION,DECODE(FOS_TYPE,'D',
    2 (select SUBSTR(CONCATENATED_SEGMENTS,1,4)
    3 FROM FND_SHORTHANd_flex_ALIAses
    4 where ALIAS_NAME = APS_CODE),'O',
    5 (select SUBSTR(CONCATENATED_SEGMENTS,1,4)
    6 FROM FND_SHORTHANd_flex_ALIAses
    7 where ALIAS_NAME = APS_CODE),'PRAS')
    8 FROM NT_DM_SMS_AR_TXN_V A ,FND_SHORTHANd_flex_ALIAses b
    9 where A.APS_CODE = B.ALIAS_NAME
    10 AND APS_CODE = 904 AND ROWNUM <= 1;

    F
    -
    DESCRIPTION
    ----------------------------------------------------------------------------------------------------
    DECO
    ----
    D
    Legacies Received - Bromley
    FFAG


    But when i use the same query in a pl/sql block or in for loop i get the following errors...i tried the whole yesterday...but i got the same errors..the errors are as follows...if any one has faced the same prob pls give the solution...thanks in advance


    ORA-06550: line 3, column 1:
    PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
    begin function package pragma procedure subtype type use
    cursor
    form current
    The symbol "begin" was substituted for "SELECT" to continue.
    ORA-06550: line 4, column 1:
    PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
    ( - + mod not null others
    avg
    count current exists max min prior sql stddev
    ORA-06550: line 6, column 28:
    PLS-00103: Encountered the symbol "," when expecting one of the following:
    . ( ) * @ % & - + / at mod rem with and or
    group having intersect minus start union where connect ||
    Dream as if u'll live forever..Live as if u'll die today.

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Hi,
    prior 9i there were two different parsers for SQL and PL/SQL. The latter didn't allow scalar subqeries in select-list.

    Move the subquery to the FROM clause or use dynamic SQL.
    Ales
    The whole difference between a little boy and an adult man is the price of toys

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