Re: What is wrong here?

From: Michael O'Shea <michael.oshea_at_tessella.com>
Date: 9 Oct 2006 13:39:09 -0700
Message-ID: <1160426349.615341.198290@k70g2000cwa.googlegroups.com>

Ana C. Dent wrote:
> 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.

And do you get the same result from your query when updated from

> > AND TAB1.col1 = '22-MAY-06'

to

AND TRUNC(TAB.col1)='22-MAY-06'

Regards
Mike

TESSELLA Michael.OShea_at_tessella.com

__/__/__/  Tessella Support Services plc
__/__/__/  3 Vineyard Chambers, ABINGDON, OX14 3PX, England
__/__/__/  Tel: (44)(0)1235-555511  Fax: (44)(0)1235-553301

www.tessella.com Registered in England No. 1466429 Received on Mon Oct 09 2006 - 15:39:09 CDT