Re: What is wrong here?

From: <pankaj_wolfhunter_at_yahoo.co.in>
Date: 9 Oct 2006 21:59:33 -0700
Message-ID: <1160456373.855891.248770@b28g2000cwb.googlegroups.com>

Michael O'Shea wrote:

> 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

Thanks for replying.

@ Ana
The output is shown after every query.
Also as you suggested, I replaced the '22-MAY-06' part with TO_DATE('22-MAY-06','DD-MON-YY') part but no luck.

@ Michael
Yes, its still giving the same mismatch even after trying with TRUNC. I dont know what is wrong here. Received on Mon Oct 09 2006 - 23:59:33 CDT