Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    8

    Unanswered: Scalar sub-query in PL/SQL

    I'm trying to put a scalar sub-query in my PL/SQL function. Something like the following:

    SELECT t1.col1, t1.col2, (SELECT t2.col3 FROM table2 t2 WHERE t2.col4=t1.col5) AS col3 FROM table1 t1 ...

    This works in SQL*PLUS but not PL/SQL. Why?

    How can I get this to work in PL/SQL?

    Thanks,
    Dylan

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    Since we can't see the code or the error my crystal ball is broken.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Mar 2004
    Posts
    8
    Originally posted by The_Duck
    Since we can't see the code or the error my crystal ball is broken.
    Here is the error:

    PLS-00103: Encountered the symbol "SELECT" when expecting one of the following: ...

    Does this help? I did some research on Google and found a couple of websites that said you can't use scalar sub-queries in PL/SQL. Is this true? (I sure hope not!)

    - Dylan

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    if you don't post your code I don't see how much help anyone can give you.

    This is what Oracle says about your error:

    PLS-00103 found 'string' but expected one of the following: 'string'"},

    Cause: This error message is from the parser. It found a token (language element) that is inappropriate in this context.

    Action: Check previous tokens as well as the one given in the error message. The line and column numbers given in the error message refer to the end of the faulty language construct.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    As Tony (Andrewst) recently pointed out to another poster, the supported syntax for Oracle SQL and PL/SQL are usually at different versions, with the SQL parser usually being ahead of the PL/SQL parser.

    Regardless of that, looking at your query, I immediately want to convert it to a join anyway (or an outer join if you so require).

    PHP Code:
    SELECT t1.col1t1.col2t2.col3
    from t1
    t2
    where t2
    .col4 t1.col5 and ..... 
    The results are the same, but the latter if far easier for the optimiiser to work with.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

Posting Permissions

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