|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
Get inside! Sample the range of functionality easily built with JMSL Library for Time Series Data Analysis, Heat Maps, Portfolio Optimization, Monte Carlo Simulation, Stock Price Charting and more. Download Now! |
|
#1
January 25th, 2005, 11:59 AM
|
|||
|
|||
|
Joining queries from view and use it generate the report
I have 4 views and all the views are made up of bunch of sql statements and I use union to join the queries.
eg: select '800' RANGE, sex,max(score) from Table1,table2,table3,table4 where sex = 'F' and test_code ='S02' and score = 800 GROUP BY sex union select '800' RANGE, sex,max(score) from Table1,table2,table3,table4 where sex = 'M' and test_code ='S02' and score between 750 and 799 GROUP BY sex so th above is one view example so I have 4 views like this and it gives score of verbal men,verbal female,Math Men,Math Female etc The first problem is if I run the views seperatley I get the oupput so the data is good but when I say select from v1,v2,v3,v4 I get different results. The second problem is I have to have parameters passing in the report and after that based on that all the views data should be in the output so I included them in the views I created and tried to run it I get lot of duplicate rows. The output should like follows: Range verbal_Men verbal_Women Math_Men Math_Women 800 4 7 8 12 750-799 2 4 7 10 *Note:I dont have a column that can be joined to hits view since all views pull out different data. Also can you give me some good example of building parameters in run time.Anybody's early response is appreciated since been stuck on this for quite a while. Thanks Prash
|
|
#2
January 25th, 2005, 03:08 PM
|
|||
|
|||
|
You should consider using a union for the views and treat it as an inline view.
Code:
select distinct col1, col2 from ( select col1, col2 from v1 union select col1, col2 from v2 ); Parameters? Code:
/* myfile.sql */
DECLARE
gender VARCHAR2(2):='&1';
age NUMBER(3):='&2';
BEGIN
select ..............
DBMS_OUTPUT.....
END;
/
Code:
usage in SQLPLUS @myfile M 10 THe parms will automatically substitute
|
|
| Viewing: Dev Shed Forums > Databases > Oracle Development > Joining queries from view and use it generate the report |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
