Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    22

    Question Unanswered: Sub-select problem in PL/SQL

    I have the following query that I am trying to run from within a PL/SQL procedure in a package.

    SELECT
    COUNT(DISTINCT SITEAPPLICATIONID)
    INTO
    iGrantedLessThan4
    FROM
    (
    SELECT
    GRW_SITEAPPLICATION.SITEAPPLICATIONID,
    GRW_SITEACTIVITY.DATEACTIVITY AS GRANT_DATE,
    (SELECT MIN(SA2.DATEACTIVITY)
    FROM GRW_SITEACTIVITY SA2
    WHERE SA2.SITEAPPLICATIONID = GRW_SITEACTIVITY.SITEAPPLICATIONID
    AND SA2.SITEACTIVITYTYPEID = 113) AS REC_DATE
    FROM
    U_GRW.GRW_SITEAPPLICATION,
    U_GRW.GRW_SITE,
    U_GRW.GRW_SITEACTIVITY
    WHERE
    (GRW_SITEAPPLICATION.SITEID = GRW_SITE.SITEID) AND
    (GRW_SITEACTIVITY.SCHEDULE=0) AND
    (GRW_SITE.AREAID = AREA.AREAID) AND
    (GRW_SITEAPPLICATION.SITEAPPLICATIONID = GRW_SITEACTIVITY.SITEAPPLICATIONID) AND
    (GRW_SITEACTIVITY.DATEACTIVITY >= DateFrom) AND
    (GRW_SITEACTIVITY.DATEACTIVITY <= DateTo) AND
    (GRW_SITEACTIVITY.SITEACTIVITYTYPEID = 114)
    )
    WHERE
    MONTHS_BETWEEN(GRANT_DATE, REC_DATE) <= 4;

    It gives me the error message when I try to compile:

    PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
    ( - + mod not null others <an identifier>
    <a double-quoted delimited-identifier> <a bind variable> avg
    count c

    When this error comes up in TOAD it highlights the third select as the problem. I beleive the problem is to do with the sub-select as when I previously tried the query in a different form it didn't like the sub-select inside the MONTHS_BETWEEN function.

    The query works happily in SQL+ with suitable constants replacing the variables. Can anybody tell me why it is not working please and/or suggest a work around?

    AT

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Sub-select problem in PL/SQL

    PL/SQL always lags behind SQL with "new" features. It looks like you are using a version of PL/SQL that doesn't support scalar subqueries (the third select is one). Assuming you cannot upgrade to a version where they are supported (e.g. 9i), you have a few choices:

    1) rewrite the query without using scalar subqueries
    2) encapsulate part of the query (including the scalar subquery) in a view, and then use the view in PL/SQL
    3) use dynamic SQL (EXECUTE IMMEDIATE or REF CURSOR)

  3. #3
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    22
    I thought I might be hitting a brick wall. I will have to do it a different way, probably with a cursor and then a further query on the results.

    Thanks for the speedy response.

    AT

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    this will probably do the job though I couldnt run it obviously

    SELECT COUNT(DISTINCT SITEAPPLICATIONID)
    INTO iGrantedLessThan4
    FROM
    (
    SELECT
    GRW_SITEAPPLICATION.SITEAPPLICATIONID,
    GRW_SITEACTIVITY.DATEACTIVITY AS GRANT_DATE,
    MIN(SA2.DATEACTIVITY) REC_DATE
    FROM
    U_GRW.GRW_SITEAPPLICATION,
    U_GRW.GRW_SITE,
    U_GRW.GRW_SITEACTIVITY,
    U_GRW.GRW_SITEACTIVITY SA2
    WHERE
    (GRW_SITEAPPLICATION.SITEID = GRW_SITE.SITEID) AND
    (GRW_SITEACTIVITY.SCHEDULE=0) AND
    (GRW_SITE.AREAID = AREA.AREAID) AND
    (GRW_SITEAPPLICATION.SITEAPPLICATIONID = GRW_SITEACTIVITY.SITEAPPLICATIONID) AND
    (GRW_SITEACTIVITY.DATEACTIVITY >= DateFrom) AND
    (GRW_SITEACTIVITY.DATEACTIVITY <= DateTo) AND
    (GRW_SITEACTIVITY.SITEACTIVITYTYPEID = 114) AND
    SA2.SITEAPPLICATIONID = GRW_SITEACTIVITY.SITEAPPLICATIONID AND
    SA2.SITEACTIVITYTYPEID = 113
    GROUP BY GRW_SITEAPPLICATION.SITEAPPLICATIONID, GRW_SITEACTIVITY.DATEACTIVITY
    )
    WHERE
    MONTHS_BETWEEN(GRANT_DATE, REC_DATE) <= 4;

Posting Permissions

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