Home » Other » Client Tools » outer join help needed urgent..
- outer join help needed urgent.. [message #38562] Fri, 26 April 2002 15:20 Go to next message
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.
- Re: outer join help needed urgent.. [message #38568 is a reply to message #38562] Fri, 26 April 2002 20:46 Go to previous messageGo to next message
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.
- Re: outer join help needed urgent.. [message #38574 is a reply to message #38562] Sat, 27 April 2002 21:21 Go to previous messageGo to next message
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.
- Re: outer join help needed urgent.. [message #38623 is a reply to message #38562] Wed, 01 May 2002 06:52 Go to previous message
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
Previous Topic: Need help Urgent.....
Next Topic: Random value within a range
Goto Forum:
  


Current Time: Thu May 01 18:53:10 CDT 2025