Re: Outer-join a subselect?

From: Michel Cadot <micadot_at_netcourrier.com>
Date: 2000/07/25
Message-ID: <8ljk4e$2qku$1@s2.feed.news.oleane.net>#1/1

You can't outer-join with a sub-query. Here's an equivalent:

SELECT p.tax_map,
si.site_no,
sa.sale_id
FROM govern.pc_parcel p,

govern.ma_site si,
govern.ma_sales sa,
(SELECT p_id, MAX(sale_id) max_sale_id

FROM govern.ma_sales
WHERE sale_date <= TO_DATE('09/30/' || TO_CHAR(si.year_id-1),'MM/DD/YYYY') AND sale_date >= TO_DATE('10/01/' || TO_CHAR(si.year_id-2),'MM/DD/YYYY') group by p_id) go
WHERE p.p_id = si.p_id

AND si.p_id = sa.p_id (+)
AND sa.p_id (+) = go.p_id
AND sa.sale_id (+) = go.max_sale_id

;

--
Have a nice day
Michel


craibuc <craibucNOcrSPAM_at_hotmail.com.invalid> a écrit dans le message :
06df7c20.3cc34d4d_at_usw-ex0105-034.remarq.com...


> I would like to outer-join a subselect.  Oracle doesn't seem to

> like this.  It there another way w/o using an in-line view?

>

> Thanks in advance.

>

> Craig Buchanan

>

> Here's a code snippet:

>

> SELECT p.tax_map,

> si.site_no,

> sa.sale_id,

> FROM govern.pc_parcel p,

> govern.ma_site si,

> govern.ma_sales sa

> WHERE p.p_id = si.p_id

> AND si.p_id = sa.p_id (+)

> AND sa.sale_id (+) = (

> SELECT MAX(sale_id)

> FROM govern.ma_sales

> WHERE p_id = sa.p_id

> AND sale_date <= TO_DATE('09/30/' || TO_CHAR

> (si.year_id-1),'MM/DD/YYYY')

> AND sale_date >= TO_DATE('10/01/' || TO_CHAR

> (si.year_id-2),'MM/DD/YYYY')

> )

> ;

>

> Craig Buchanan

> Cogniza, Business Intelligence Specialists

> www.cogniza.com

> +1.612.824.5858

>

> -----------------------------------------------------------

>

> Got questions?  Get answers over the phone at Keen.com.

> Up to 100 minutes free!

> http://www.keen.com

>

Received on Tue Jul 25 2000 - 00:00:00 CDT