Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: What is wrong here?
pankaj_wolfhunter_at_yahoo.co.in wrote in news:1160392498.663768.160350
@m73g2000cwd.googlegroups.com:
> 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.
What output? I do not see any output here? Match what to what?
>
> Can anyone please tell me what is wrong in my query?
My crystal ball is in the shop for repair.
How are we (TINW) to know what the desired output is supposed to be?
FWIW - in Oracle characters enclosed in single quote marks (like '22-MAY-06') are string datatypes; not DATE datatype. Received on Mon Oct 09 2006 - 13:45:46 CDT