Re: What is wrong here?

pankaj_wolfhunter_at_yahoo.co.in wrote:

> 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.

still stuck here. any1? Received on Wed Oct 11 2006 - 00:20:32 CDT