Re: Join of 4 tables

From: Morten Tangaa <morten_at_tangaa.com>
Date: Fri, 26 Jan 2001 16:13:16 -0800
Message-ID: <yooc6.77051$Wq1.32458434_at_nnrp5-w.sbc.net>

You're an e-Consultant, right?
Have you ever created a (Oracle) SQL join before? What are you trying to accomplish? The GROUP BY makes no sense, since you mention all fields (and no aggrate function) effectively changing it to an ORDER BY, there is no such thing as a JOIN (or CROSS or ON) key word in Oracle SELECT statements and the RTRIM function effectively disables indexes on those columns ...

Anyway here is an equivalent SELECT statement:

SELECT S.start_date
     , B.item_id
     , B.category
     , B.vendor
     , B.subclass
     , B.class
     , B.department
     , C.location_id
     , C.region
     , C.national_code
     , A.quantity
FROM   i_item_hierarchy          B
     , i_location_hierarchy      C
     , i_bucket_spec             S
     , i_demand_forecast_itm_loc A
WHERE  B.item_id            = A.item_id
AND    C.location_id        = A.location_id
AND    rtrim(B.item_id)     = 'ITEM002001'

AND rtrim(C.location_id) = 'FIE209'
AND TRUNC(S.start_date) IN (

         to_date('01/01/2000', 'DD/MM/YYYY')
       , to_date('02/01/2000', 'DD/MM/YYYY')
       , to_date('03/01/2000', 'DD/MM/YYYY')
       )
AND    A.start_date (+)     = S.start_date
ORDER BY B.item_id
       , C.location_id
       , B.category
       , B.vendor
       , B.subclass
       , B.class
       , B.department
       , C.region
       , C.national_code
       , S.start_date
       , A.quantity;

This assumes the outer join on A.start_date with S.start_date (no timestamp just the date).

Good luck!
Morten

dineshvp_at_india.dharma.com wrote in message <94hac5$d1s$1_at_nnrp1.deja.com>...
>Hello,
>
> I have the following SQL query
>
>SELECT S.start_date, B.item_id, B.category, B.vendor, B.subclass,
> B.class, B.department,C.location_id, C.region, C.national_code,
> A.quantity
>FROM (i_item_hierarchy B cross join i_location_hierarchy C
> cross join i_bucket_spec S )
> left outer join i_demand_forecast_itm_loc A
>ON A.start_date = S.start_date
>WHERE B.item_id = A.item_id AND C.location_id = A.location_id AND
> ( rtrim(B.item_id) = 'ITEM002001' AND rtrim(C.location_id) = 'FIE209')
> AND S.start_date IN (to_date('01/01/2000'),to_date('02/01/2000'),
> to_date('03/01/2000') )
>GROUP BY B.item_id,C.location_id,B.category, B.vendor, B.subclass,
> B.class, B.department, C.region, C.national_code, S.start_date,
> A.quantity;
>
>Here as I mentioned I want a cartesian product of tables B, C and S
>joined as Left Outer Join with the table A. But the syntax I specified
>is not supported in Oracle. Can somebody tell me the equivalent
>statement using Oracle supported syntax.
>
>Thanks in advance for any help.
>dinesh
>
>
>Sent via Deja.com
>http://www.deja.com/
>
Received on Sat Jan 27 2001 - 01:13:16 CET