Common Table Expression and subquery, table alias problem
530947Sep 14 2006 — edited Sep 14 2006I 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?