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