If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > How to stored the result query based on parameters into temp table or view

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,612
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.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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/
Quote:
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.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Location: Massachusetts
Posts: 5,471
Have you asked the professor about why you are getting that error?
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,612
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.
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
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: Data Comparability!

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?
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
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???
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,612
>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.
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,612
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.
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On