OraFAQ Forum: Client Tools » outer join help needed urgent..

Home » Other » Client Tools » outer join help needed urgent..

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

outer join help needed urgent.. [message #38562]

Fri, 26 April 2002 15:20

shreya
Messages: 15
Registered: March 2001

Junior Member

Hi,
Here is my quey.

DROP TABLE my_acc_table;
CREATE TABLE my_acc_table AS
SELECT a.*
FROM schma1.user_table@x1 a,
schma2.user_table@x2 b
WHERE
a.start_date = '01-jan-02'
and b.start_date = '01-jan-02'
and a.user_id = b.user_id
and ABS(a.amount-b.amount) /
DECODE(a.amount,0,1,a.amount) >= 0.05;

I have to write outer join for this query.
user_id's may be different in both the schema.

I need both the left and the right outer join for this query.
If anyone knows how to do it please send it.
I would be very thankful to u.

Regards,
Sushma.

Report message to a moderator

Re: outer join help needed urgent.. [message #38568 is a reply to message #38562]

Fri, 26 April 2002 20:46

Saga
Messages: 51
Registered: April 2002

Member

Lets first look at outer join implementation
Rule 1: Outer join(+) applies to side(column) which is deficient in data.
Rule 2: Outer join is applied to only one side in a given condition be it left side or right side.You can apply it to either left or right side in a condition but not to both.

example

and a = b(+)
and c(+) = d
is correct

But
and a(+) = b(+) --wrong
and c(+) = d
is not

Hope it will help.

Report message to a moderator

Re: outer join help needed urgent.. [message #38574 is a reply to message #38562]

Sat, 27 April 2002 21:21

Vikas Gupta
Messages: 115
Registered: February 2002

Senior Member

Hello,

Why don't you use the UNION clause.
First select statement with one outer join
union
second select statement with other outer join.

Regards,

Vikas Gupta.

Report message to a moderator

Re: outer join help needed urgent.. [message #38623 is a reply to message #38562]

Wed, 01 May 2002 06:52

oraboy
Messages: 97
Registered: October 2001

Member

Have a look at SQL new syntax available from 9i onwards. The messy and confusing (atleast for newbies) outer join operations (placing + this side or that side) are replaced by a simple syntax OUTER JOIN

select ..from table a LEFT OUTER JOIN table b

how abt that?

Oraboy

Report message to a moderator

Previous Topic:	Need help Urgent.....
Next Topic:	Random value within a range

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 18:16:13 CDT 2024