Re: What is wrong here?

From: <pankaj_wolfhunter_at_yahoo.co.in>
Date: 11 Oct 2006 08:35:20 -0700
Message-ID: <1160580920.849620.97860@k70g2000cwa.googlegroups.com>

Charles Hooper wrote:

> pankaj_wolfhunter_at_yahoo.co.in wrote:
> > 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?

>

> Looking at your second SQL statement, that returned a result of 12:
> 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'

>

> The above appears to be equivalent to this, which is written in the
> older style join syntax:
> SELECT
> SUM(column_name)
> FROM
> TABLE1 TAB1,
> TABLE2 TAB2,
> TABLE3 TAB3
> WHERE
> TAB1.col1 = TAB2.col1(+)
> AND TAB1.col2 = TAB2.col2(+)
> AND TAB1.col3 = TAB2.col3(+)
> AND TAB1.col1 = TAB3.col1(+)
> AND TAB1.col2 = TAB3.col2(+)
> AND TAB1.col3 = TAB3.col3(+)
> AND TAB2.col4 = TAB3.col4(+)
> AND TAB1.col5 <> 0
> AND TAB1.col6 IN ('M','GM')
> AND TAB1.col1 = '22-MAY-06'
> AND TAB2.col1 = '22-MAY-06'
> AND TAB3.col1 = '22-MAY-06';

>

> The "AND TAB2.col1 = '22-MAY-06'" and "AND TAB3.col1 = '22-MAY-06'" in
> the WHERE clause will effectively force an equi-join between tables
> TABLE1 and tables TABLE2 and TABLE3, since it will not permit COL1 in
> TABLE2 or TABLE3 to be null as a result of the outer join. Instead,
> you might try modifying the WHERE so that the last 3 lines read:
> AND TAB1.col1 = '22-MAY-06'
> AND TAB2.col1(+) = '22-MAY-06'
> AND TAB3.col1(+) = '22-MAY-06';

>

> I am not suggesting that this is the final solution, just another
> possible approach to finding the solution.

>

> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.

Thanks Charles. I'll try that and get back to you. Received on Wed Oct 11 2006 - 10:35:20 CDT