puneetbans_at_gmail.com wrote:
> Ashish wrote:
> > Have you tried using the new ANSI SQL syntax introducted in 9i. It is
> > something like SELECT .... FROM table_a OUTER JOIN table_b ON ...
> > (Sorry, please check the exact syntax).
> Here's my simplifed query
>
> select
> b.id
> from
> tab1 a,
> tab2 b,
> tab3 c
> where
> a.dig_asset_i = c.parnt_dig_asset_i(+)
> and
> (select a1.dig_asset_i from tab1 a1 where a1.dig_asset_i =
> c.chld_dig_asset_i) = b.dig_asset_i
>
> I want the join with the sub query to b.dig_asset_i to be an outer join
> but Oracle doesn't allow it. With the above query, I don't get any rows
> for dig_asset_i in tab1 for which there is no dig_asset_i in tab2.
> Using ANSI syntax doesn't help either as then I have to move the join
> condition to the 'From' clause and correlated sub query doesnt' work
> there.
>
> The table structures are
>
> tab 1 - dig_asset_i
> tab 2 - dig_asset_i, id
> tab 3 - parnt_dig_asset_i, chld_dig_asset_i (these can be compared to
> dig_asset_i in tab1. Basically I am trying to find the data in tab2 for
> dig_asset_i which are children of dig_asset_i mentioned in tab1. tab1
> is the master table for dig_asset_i and contains both parent and
> children dig_asset_i. tab3 is the table containing the parent child
> relationship between these tables.
>
> Puneet
>
Try simplify the SQL statement again. This SQL statement should be
equivalent to the one that you posted:
SELECT
B.ID
FROM
TAB1 A,
TAB1 A1,
TAB2 B,
TAB3 C
WHERE
A.DIG_ASSET_I=C.PARNT_DIG_ASSET_I(+)
AND C.CHLD_DIG_ASSET_I=A1.DIG_ASSET_I
AND A1.DIG_ASSET_I=B.DIG_ASSET_I;
It appears that the last two restrictions in the WHERE clause make it
unnecessary to reference TAB1 a second time. The SQL statement can
then be refined again:
SELECT
B.ID
FROM
TAB1 A,
TAB2 B,
TAB3 C
WHERE
A.DIG_ASSET_I=C.PARNT_DIG_ASSET_I(+)
AND C.CHLD_DIG_ASSET_I=B.DIG_ASSET_I;
The above should give you problems if an outer join is created between
table A and table C, so you will likely need to format it like this:
SELECT
B.ID
FROM
TAB1 A,
TAB2 B,
TAB3 C
WHERE
A.DIG_ASSET_I=C.PARNT_DIG_ASSET_I(+)
AND C.CHLD_DIG_ASSET_I=B.DIG_ASSET_I(+);
Maybe I am misunderstanding the description of your problem?
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Thu Sep 28 2006 - 20:38:41 CDT
