Home » SQL & PL/SQL » SQL & PL/SQL » Selecting from multiple queries in one statement
Selecting from multiple queries in one statement [message #332976] Thu, 10 July 2008 04:09 Go to next message
ydsouza
Messages: 3
Registered: April 2008
Junior Member
Hi,
Please have a look at the code below. What i am trying to do in this is to extract a similar information twice but using two queries a and b as sources; in these two queries I am just changing the condition SDATE>=sysdate-14/24 to SDATE>=sysdate-20/24. When I use just query I get a different result from when I try to combine the 2 queries as below. Any reason for this? Thank you so much.
select SUM(a.LESS_20_12hrs) LESS_20_12hrs, SUM(b.LESS_20_18hrs) LESS_20_18hrs from (select 
case 
when z.PTTCH<20
 then 1 else 0
 end LESS_20_12hrs from
(select y.CELL, y.NE, round(y.TCH*100/y.DTCH,2) PTTCH from
(select 
unique x.CELL, x.NE, SUM(x.TCH) over (partition by x.CELL) TCH, SUM(x.DTCH) over (partition by x.CELL) DTCH
from ERICSSON_GSM.CELLSTATS_60 x
where x.SDATE >= sysdate-14/24
and
x.DTCH!=0
) y
) z
) a, 
(select 
case 
when w.PTTCH<20
 then 1 else 0
 end LESS_20_18hrs from
(select v.CELL, v.NE, round(v.TCH*100/v.DTCH,2) PTTCH from
(select 
unique u.CELL, u.NE, SUM(u.TCH) over (partition by u.CELL) TCH, SUM(u.DTCH) over (partition by u.CELL) DTCH
from ERICSSON_GSM.CELLSTATS_60 u
where u.SDATE >= sysdate-20/24
and
u.DTCH!=0
) v
) w
) b


[MERGED by LF]

[Updated on: Thu, 10 July 2008 06:40] by Moderator

Report message to a moderator

Re: selecting from multiple queries in one statement [message #332984 is a reply to message #332976] Thu, 10 July 2008 04:27 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
How many rows does subquery A return?
How many rows does subquery B return?
As you did not specify any join conditions between A and B, you will get cartesian product (A * B rows).

Maybe you could do the SUM inside the A and B subqueries (they would return just one row).
You can add the condition "SDATE>=sysdate-14/24" (more restrictive one) into CASE and use two aggregate columns instead of two queries.
Re: selecting from multiple queries in one statement [message #332998 is a reply to message #332976] Thu, 10 July 2008 04:47 Go to previous message
dwarak.k
Messages: 61
Registered: June 2008
Location: Hyderabad
Member
Are you sure this is the qry you wanted to write?
There is no join condition between the two inline views a and b

It would have been easier if the code was formatted

SELECT SUM(a.less_20_12hrs)  less_20_12hrs, 
       SUM(b.less_20_18hrs)  less_20_18hrs 
FROM   (SELECT CASE 
                 WHEN z.pttch < 20 THEN 1 
                 ELSE 0 
               END less_20_12hrs 
        FROM   (SELECT y.cell, 
                       y.ne, 
                       Round(y.tch * 100 / y.dtch,2) pttch 
                FROM   (SELECT UNIQUE x.cell, 
                                      x.ne, 
                                      SUM(x.tch) 
                                        OVER(PARTITION BY x.cell ) tch, 
                                      SUM(x.dtch) 
                                        OVER(PARTITION BY x.cell ) dtch 
                        FROM   ericsson_gsm.cellstats_60 x 
                        WHERE  x.sdate >= SYSDATE - 14 / 24 
                               AND x.dtch != 0) y) z) a, 
       (SELECT CASE 
                 WHEN w.pttch < 20 THEN 1 
                 ELSE 0 
               END less_20_18hrs 
        FROM   (SELECT v.cell, 
                       v.ne, 
                       Round(v.tch * 100 / v.dtch,2) pttch 
                FROM   (SELECT UNIQUE u.cell, 
                                      u.ne, 
                                      SUM(u.tch) 
                                        OVER(PARTITION BY u.cell ) tch, 
                                      SUM(u.dtch) 
                                        OVER(PARTITION BY u.cell ) dtch 
                        FROM   ericsson_gsm.cellstats_60 u 
                        WHERE  u.sdate >= SYSDATE - 20 / 24 
                               AND u.dtch != 0) v) w) b 
Previous Topic: Reg Updating a column with the sequence number (merged)
Next Topic: Does Oracle handling temp tables differently in 10g?
Goto Forum:
  


Current Time: Fri Dec 02 16:23:32 CST 2016

Total time taken to generate the page: 0.36237 seconds