Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2005
    Posts
    54

    How to stored the result query based on parameters into temp table or view

    Hello,

    I am not sure how to store the first query result based on parameters into temp table or view in which use for another query with Ref_cursor. The reason I need PL/SQL code to automate above processes because I cannot add aggregate function such AVG, MIN, MAX to the first complex subquery due to display the error: ORA-00937: not a single-group group function when calculating Please give me an example a store procedure that cover my above issue. Thanks in advance.
    Last edited by avt2k6; 04-02-12 at 23:31.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,616
    HUH?
    We speak SQL so please do the same.
    post CREATE TABLE
    post INSERT with sample test data
    post expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
    I have NO idea what you are talking about.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Don't say, show. Don't promise, prove.

  3. #3
    Join Date
    Mar 2007
    Posts
    615
    In Oracle, you rarely need "temporary tables". Yes, there are some situations, when they are useful, but not the one you state.

    As views are just stored queries, you may make view over "first complex subquery" and use it in "another query with Ref_cursor". Sounds quite overcomplicated in comparison with single query containing that subquery though.

    You will probably still face that error, but, according to your description of the problem, the only recommendation is: fix that overall query.

    http://ora-00937.ora-code.com/
    ORA-00937: not a single-group group function
    Cause: A SELECT list cannot include both a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, and an individual column expression, unless the individual column expression is included in a GROUP BY clause.
    Action: Drop either the group function or the individual column expression from the SELECT list or add a GROUP BY clause that includes all individual column expressions listed.

  4. #4
    Join Date
    Dec 2005
    Posts
    54
    Hello,

    I am so sorry for my uncleared question. Here are the SP, Tables and detailed results as folllowing:

    1. Stored procedure
    ================================================== ===============
    CREATE OR REPLACE PROCEDURE ERDMS.SP_RET_SCHOOL
    (P_school_id IN Varchar,
    P_test_id IN Number,
    P_FromDate IN Date,
    P_ToDate IN Date,
    RS OUT Sys_Ref_Cursor)
    IS

    P_RET_STATUS VARCHAR2(5);

    BEGIN

    OPEN RS FOR

    SELECT DISTINCT a.school_id, b.test_id, b.course, b.test_score, a.school_name, b.rank_number, a.location, b.test_date
    FROM tblSchool a, tblRank b
    WHERE a.a.school_id = b.school_id
    and a.school_id = P_school_id
    and b.test_id = P_test_id
    and b.test_date >= P_FromDate AND b.test_date <= P_ToDate

    P_RET_STATUS:=SQLCODE;

    EXCEPTION

    WHEN NO_DATA_FOUND THEN
    P_RET_STATUS:=SQLCODE;
    WHEN OTHERS THEN
    P_RET_STATUS:=SQLCODE;

    END SP_FILL_DMR_DS;
    /

    -------------------------------------------------------------------------------------
    2. Tables

    tblSCHOOL
    ======
    school_id
    school_name
    room_location

    tblRANK
    ======
    rank_id
    school_id
    course
    test_id
    test_name
    test_date
    test_score
    rank_number


    The SP result as following:

    school_id | test_id | course | test_score | school_name | rank_number | room_location | test_date
    ================================================== ================
    A|1|Math|70|ABC|2|north|03/01/2012
    A|1|Math|90|ABC|2|south|03/31/2012
    A|2|English|50|ABC|2|east|03/01/2012
    A|2|English|90|ABC|2|west|03/02/2012
    A|2|English|40|ABC|2|north|03/05/2012
    A|2|English|100|ABC|2|south|03/10/2012
    A|2|English|65|ABC|2|east|03/15/2012
    A|2|English|85|ABC|2|west|03/20/2012
    A|2|English|35|ABC|2|north|03/28/2012
    A|2|English|77|ABC|2|south|03/31/2012

    I would like to calculate average(test_score), min(test_score) and max(test_score) in a month based on school_id, test_id and course as following:

    school_id | test_id | course | avg(test_score) | min(test_score) | max(test_score)
    --------------------------------------------------------------------------------
    A | 1 | Math | 80 | 70 | 90
    A | 2 | English |67 | 35 | 100

    If I can store the output of store procedure in a view or table and use them for the following query:

    SELECT school_id, test_id, course, avg(test_score), min(test_score), avg(test_score)
    FROM my_view;

    I can get the above results as I expect;

    Furthermore, what I need to push the data on the monthly report are not only school_id, test_id, course, avg(test_score), min(test_score), max(test_score) but also school_name, rank_number, and location. If I add the aggregate function to the query in store procedure that will display the error ORA-00937: not a single-group group function. How do I can resolve my issues? I am newbie to Oracle PL/SQL. Is it the combining scalar and aggregate values ? Finally, I hope that you can get what I need helps. Thanks in advance.
    Last edited by avt2k6; 04-03-12 at 09:22.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,485
    Have you asked the professor about why you are getting that error?

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,616
    We speak SQL so please do the same.
    post CREATE TABLE
    post INSERT with sample test data
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Don't say, show. Don't promise, prove.

  7. #7
    Join Date
    Mar 2007
    Posts
    615
    Quote Originally Posted by avt2k6 View Post
    SELECT school_id, test_id, course, avg(test_score), min(test_score), avg(test_score)
    FROM my_view;
    I do not understand why you created this new topic instead of continuing in the previous one: http://www.dbforums.com/oracle/16774...arability.html

    Just compare your query with the one anacedent posted there. Is not your one missing something (the third row)?

    For explanation of its behaviour, just start studying your course materials. Alternatively you may find it in SQL Language Reference book. It is available with other Oracle documentation e.g. online on http://tahiti.oracle.com/
    Quote Originally Posted by avt2k6 View Post
    Is it the combining scalar and aggregate values ?
    No, you cannot do that, and it is clearly stated in the error description I posted. Just think about it - your query without GROUP BY clause and with aggregates will return one row - which value would you put to non-aggregated columns then?

  8. #8
    Join Date
    Dec 2005
    Posts
    54
    Hello,

    Thank you for your inputs. I got the error and I knew that I could not add the aggregate functions to the SP query to get my expected final result. So, how do I store the SP result into table or view on the fly with PL/SQL in order to run the second query???

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,616
    >I knew that I could not add the aggregate functions to the SP query to get my expected final result.
    above is not correct

    >So, how do I store the SP result into table or view on the fly with PL/SQL in order to run the second query???
    No additional table or view is required.

    We speak SQL so please do the same.
    post CREATE TABLE
    post INSERT with sample test data
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Don't say, show. Don't promise, prove.

  10. #10
    Join Date
    Dec 2005
    Posts
    54
    Never mind. I found the way to work around my problem by writing several stored procedure to get all report data that I need.

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,616
    Please post solution here.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Don't say, show. Don't promise, prove.

  12. #12
    Join Date
    Dec 2005
    Posts
    54
    Here are the solution:created two different stored procedure with bellowed queries:

    SP 1:
    SELECT a.school_id, b.test_id, b.course, avg(test_score), min(test_score), avg(test_score)
    FROM tblSchool a, tblRank b
    WHERE a.a.school_id = b.school_id and a.school_id = P_school_id and b.test_id = P_test_id and b.test_date >= P_FromDate AND b.test_date <= P_ToDate
    GROUP BY school_id, test_id, course

    SP2:
    SELECT DISTINCT a.school_id, b.test_id, b.course, b.test_score, a.school_name, b.rank_number, a.location, b.test_date
    FROM tblSchool a, tblRank b
    WHERE a.a.school_id = b.school_id
    and a.school_id = P_school_id
    and b.test_id = P_test_id
    and b.test_date >= P_FromDate AND b.test_date <= P_ToDate

Posting Permissions

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