| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
Home -> Community -> Usenet -> c.d.o.misc -> What is wrong here?
Greetings,
I have a query using left outer join as
SELECT SUM(column_name)
FROM (
TABLE1 PARTITION(T_20060522) TAB1
LEFT OUTER JOIN TABLE2 PARTITION(T_20060522) TAB2
ON (TAB1.col1 = TAB2.col1
AND TAB1.col2 = TAB2.col2
AND TAB1.col3 = TAB2.col3)
)
LEFT OUTER JOIN TABLE3 (T_20060522) TAB3
ON (TAB1.col1 = TAB3.col1
AND TAB1.col2 = TAB3.col2
AND TAB1.col3 = TAB3.col3
AND TAB2.col4 = TAB3.col4)
WHERE TAB1.col5 <> 0
AND TAB1.col6 IN ('M','GM')
Output - 16
There's a need to remove the partition name and include the date
explicitly (col1 is the date column),
so when I use the above query without partition and using date instead
as
SELECT SUM(column_name)
FROM (
TABLE1 TAB1
LEFT OUTER JOIN TABLE2 TAB2
ON (TAB1.col1 = TAB2.col1
AND TAB1.col2 = TAB2.col2
AND TAB1.col3 = TAB2.col3)
)
LEFT OUTER JOIN TABLE3 TAB3
ON (TAB1.col1 = TAB3.col1
AND TAB1.col2 = TAB3.col2
AND TAB1.col3 = TAB3.col3
AND TAB2.col4 = TAB3.col4)
WHERE TAB1.col5 <> 0
AND TAB1.col6 IN ('M','GM')
-- here is the date part
AND TAB1.col1 = '22-MAY-06'
AND TAB2.col1 = '22-MAY-06'
AND TAB3.col1 = '22-MAY-06'
Output - 12
I am not able to match the output here.
Can anyone please tell me what is wrong in my query?
TIA
DB version info:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
Received on Mon Oct 09 2006 - 06:14:58 CDT
![]() |
![]() |