Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2011
    Posts
    11

    Unanswered: Using full outer join in Oracle 8i

    Hi All,

    I want to run the below sql query in Oracle 8i using the concept of full outer join. Please let me know how I can do this. I guess I have to use union. If yes please advise how do i go about it.

    SQL> select nvl(t1.emp_no,t3.emp_no) emp_no, nvl(t1.work_date,t3.work_date) work_date, t1.time1 t_in, t3.time1 t_out
    2 from
    3 (select emp_no, work_date, time1
    4 from data_trans
    5 where time_type = 1) t1
    6 full outer join
    7 (select emp_no, work_date, time1
    8 from data_trans
    9 where time_type = 3) t3
    10 on t1.emp_no = t3.emp_no
    11 and t1.work_date = t3.work_date
    12 order by emp_no, work_date, t_in;
    full outer join
    *
    ERROR at line 6:
    ORA-00933: SQL command not properly ended

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2011
    Posts
    20
    Quote Originally Posted by kmack View Post
    I want to run the below sql query in Oracle 8i using the concept of full outer join
    Let me try to help because:

    1. This is a forum where people post questions here to seek help, no matter how stupid the person is
    2. The pages in this forum including this post will be pick up by Google, someone else googling a topic using keywords from this page will get this page that has no answer and yet the suggested action it to google it!
    3. I am new to Oracle, I try to teach other while I hope others will teach me
    Here is what I found:

    To write a query that performs an outer join and returns all rows from A and B, extended with nulls if they do not satisfy the join condition (a full outer join), use the FULL [OUTER] JOIN syntax in the FROM clause.

    Code:
    select * from table1 a 
    full join table2 b
    on a.col1= b.col1
    You can use a full outer join to what you want to do but you need to brush up your SQL syntax to solve the syntax error

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    ANSI syntax is not supported in 8I and a full outer join is not directly supported.

    see the following link. It explains it very will.

    http://asktom.oracle.com/pls/asktom/...:4963137609733

    You can also do something like this

    Code:
    select *    from t1, t2  
    where t1.key = t2.key (+)  
    UNION ALL  
    select *    
    from t1, t2  
    where t1.key(+) = t2.key    
    and t1.key IS NULL;
    Last edited by beilstwh; 10-04-11 at 18:06.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •