Selecting from multiple queries in one statement [message #332976] |
Thu, 10 July 2008 04:09  |
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   |
flyboy
Messages: 1903 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  |
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
|
|
|