Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> What is wrong here?

What is wrong here?

From: <pankaj_wolfhunter_at_yahoo.co.in>
Date: 9 Oct 2006 04:14:58 -0700
Message-ID: <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.

Can anyone please tell me what is wrong in my query?

TIA DB version info:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi PL/SQL Release 10.2.0.1.0 - Production Received on Mon Oct 09 2006 - 06:14:58 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US