Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2002
    Posts
    5

    Unanswered: Top-N vales for Group Functions in Oracle 7

    Hi again ....

    I am still struggling to get the Top-N values for columns which are a result of applying aggreate functions like SUM, AVG etc on database table columns.

    I must add that all soluntions given to me would work perfectly in Oracle 8i or above. But I am using Oracle 7.

    I have wriiten a query on the lines of generic Top-N query
    ... But it is giving me Invalid column name .

    Can anyboby figure this out ....

    Here is the query

    SELECT
    asse_no,
    SUM( round(gross_amt + srg + hbt ) ) as outs
    FROM
    pls_demand_1 p
    having
    20 > ( select count(*)
    from
    (
    select asse_no
    from pls_demand_1
    having SUM(round(gross_amt + srg + hbt )) > 100000 --SUM(p.gross_amt + p.srg + p.hbt ) /* ERROR HERE, neither p.outs is working here */
    group by asse_no
    )
    )
    group by asse_no
    order by 2 desc


    I want to reference p.outs in the inner-inner subquery .... but Oracle tells me it is an invalid column name.

    I have just given 100000 in it's place so that it works ...


    Plese Help.

    Thanks in advance ....

    Regards,
    Suddha Satta Ray
    Calcutta
    INDIA

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

    Re: Top-N vales for Group Functions in Oracle 7

    Your query cannot work, as it is trying to reference its own result in a subquery used to determine its result...

    It's a long time since I used Oracle 7, but can you not even do this:

    SELECT * FROM(
    SELECT
    asse_no,
    SUM( round(gross_amt + srg + hbt ) ) as outs
    FROM
    pls_demand_1 p
    group by asse_no
    order by 2 desc
    ) WHERE ROWNUM <= 20

    ?

    Or if that won't work, you could create a view:

    CREATE VIEW v AS
    SELECT
    asse_no,
    SUM( round(gross_amt + srg + hbt ) ) as outs
    FROM
    pls_demand_1 p
    group by asse_no;

    Then:

    SELECT *
    FROM v v1 WHERE
    20 > (SELECT COUNT(*) FROM v v2 WHERE v2.outs > v1.outs);

Posting Permissions

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