Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Common Table Expression and subquery, table alias problem

530947Sep 14 2006 — edited Sep 14 2006
I am using a CTE and subquery expression. I come across interesting situtation for me.
problem is FAIL column of select.
If I use this expression.

with tempTable AS (
SELECT
TO_CHAR(INSERTION_DATE,'dd') as GUN,
SESSION_TIME
FROM voip_amsvoice
where
TO_CHAR(INSERTION_DATE,'ddmm')
IN ('1209' ,'1309','1409')
)

SELECT
GUN
,
(
SELECT COUNT(*) FROM TempTable
WHERE
SESSION_TIME = 0
AND GUN = t.Gun
) AS FAIL
,COUNT(*) AS TOPLAM_CAGRI
,SUM(SESSION_TIME)/60 AS SURE_DK
,AVG(SESSION_TIME) AS AVG_DK
,MAX(SESSION_TIME)/60 AS MAX_DK
FROM tempTable t
GROUP BY GUN

GUN FAIL TOPLAM_CAGRI SURE_DK AVG_DK MAX_DK
--- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
12 836 1712 2756,617 96,61 59,15
13 493 925 1196,35 77,601 29,633
14 708 1391 1507,817 65,039 25,517

3 rows selected

I get this output as I expect.

but if I do not rename tempTable as t in select query as below.

with tempTable AS (
SELECT
TO_CHAR(INSERTION_DATE,'dd') as GUN,
SESSION_TIME
FROM voip_amsvoice
where
TO_CHAR(INSERTION_DATE,'ddmm')
IN ('1209' ,'1309','1409')
)

SELECT
GUN
,
(
SELECT COUNT(*) FROM TempTable
WHERE
SESSION_TIME = 0
AND GUN = tempTable.Gun
) AS FAIL
,COUNT(*) AS TOPLAM_CAGRI
,SUM(SESSION_TIME)/60 AS SURE_DK
,AVG(SESSION_TIME) AS AVG_DK
,MAX(SESSION_TIME)/60 AS MAX_DK
FROM tempTable
GROUP BY GUN

GUN FAIL TOPLAM_CAGRI SURE_DK AVG_DK MAX_DK
--- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
12 2037 1712 2756,617 96,61 59,15
13 2037 925 1196,35 77,601 29,633
14 2037 1391 1507,817 65,039 25,517

3 rows selected

2037 = 836 +493 + 708
now fail rows are selected as all rows in query. I used bold in diff. Why table aliasing change results any idea?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 12 2006
Added on Sep 14 2006
2 comments
687 views