Very Computer

Can someone assist me? Sorry if this is too complicated.

I know the ansi syntax for full outer joins does not work in Oracle.
(I'm using ODBC BTW)

Can someone tell me
1) the syntax and
2) can I retrict the scope of the result set as mentioned below?

Thanks
Jeff Kish

table1 part (code, number, alt_num, AlphaNumeric)
table2 altpart(alt_num, AlphaNumeric)

table1 =        ('code1','part1','alt_num1','part1'),
        ('code2','part_2',NULL,','part2')
table2 =        ('alt_num1','altnum1'),
        ('altnum2','altnum2')

results of full outer join should be? 1st table1 row joined with 1st
table2 row and then 2nd table1 row and then 2nd table2 row, not
necessarily in that order:

'code1','part1','alt_num1','part1',     'alt_num1','altnum1'
'code2','part_2',NULL,'part2',  NULL,NULL
NULL,NULL,NULL,NULL,            'altnum2','altnum2'

About limiting the results, .. could I somehow get only entries which
were related to a certain value of number, say 'part1'

Thanks again,
Jeff Kish

You have mentioned different problems.

On ODBC, the original versions did not allow outer joins except as part
of passthru SQL statements. I do not use ODBC, so I am uncertain if
outer joins are supported at the momement.

On syntax, SQL Reference manaul -- chapter 5 on queries and subqueries.

Outer Joins

An outer join extends the result of a simple join. An outer join
returns all rows that satisfy the join condition and those rows from
one table for which no rows from the other satisfy the join condition.
Such rows are not returned by a simple join. To write a query that
performs an outer join of tables A and B and returns all rows
from A, apply the outer join operator (+) to all columns of B in the
join condition.

For all rows in A that have no matching rows in B, Oracle returns NULL
for any select list expressions containing columns of B. See the syntax
for an outer join in "SELECT and Subqueries" on page 7-569.

Outer join queries are subject to the following rules and restrictions:
n The (+) operator can appear only in the WHERE clause or, in the
context of left-correlation (that is, when specifying the TABLE clause)
in the FROM clause, and can be applied only to a column of a table or
view.

[] If A and B are joined by multiple join conditions, you must use the
(+) operator in all of these conditions. If you do not, Oracle will
return only the rows resulting from a simple join, but without a
warning or error to advise you that you do not have the results of an
outer join.
[] The (+) operator can be applied only to a column, not to an
arbitrary expression. However, an arbitrary expression can contain a
column marked with the (+) operator.
[] A condition containing the (+) operator cannot be combined with
another condition using the OR logical operator.
[] A condition cannot use the IN comparison operator to compare a column
marked with the (+) operator with an expression.
[] A condition cannot compare any column marked with the (+) operator
with a subquery.

If the WHERE clause contains a condition that compares a column from
table B with a constant, the (+) operator must be applied to the column
so that Oracle returns the rows from table A for which it has generated
NULLs for this column. Otherwise Oracle will return only the results of
a simple join. In a query that performs outer joins of more than two
pairs of tables, a single table can be the NULL-generated table for
only one other table. For this reason, you cannot apply the (+)
operator to columns of B in the join condition for A and B and
the join condition for B and C.

There are examples in chapter 7 under select and subqueries...

Reading is a good thing.

--
Michael Krolewski
Rosetta Inpharmatics

              Usual disclaimers

Sent via Deja.com
http://www.deja.com/

And here is a brief example of the ODBC join esacpe usage:

Oracle Join:
=========
select dept.deptno, sum(sal)
from dept, emp
where emp.deptno(+) = dept.deptno
group by dept.deptno

ODBC Join:
=========
select dept.deptno, sum(sal)
from {oj dept left outer join emp
on dept.deptno = emp.deptno}
group by dept.deptno

and now a 3 table join...

--SQL to create tables and populate

create table table_one (one_id integer, one_desc char(20));
create table table_two (two_id integer, two_desc char(20));
create table table_three (three_id integer, three_desc char(20));
insert into table_one (one_id, one_desc) values(1, 'TABLE1-1')
insert into table_one (one_id, one_desc) values(2, 'TABLE1-2')
insert into table_one (one_id, one_desc) values(3, 'TABLE1-3')
insert into table_two (two_id, two_desc) values(1, 'TABLE2-1')
insert into table_two (two_id, two_desc) values(3, 'TABLE2-3')
insert into table_two (two_id, two_desc) values(4, 'TABLE2-4')
insert into table_three(three_id, three_desc) values(1, 'TABLE3-1')
insert into table_three(three_id, three_desc) values(2, 'TABLE3-2')
insert into table_three(three_id, three_desc) values(5, 'TABLE3-5')

--the select statement, (can be done in ODBCTEST)

select one_id, one_desc, two_id, two_desc, three_id, three_desc from
{oj table_one left outer join table_two on one_id=two_id},
{oj table_one left outer join table_three on one_id=three_id}

--
-cheers
  DW
--------------------------------------------------------------------
"It is a kind of good deed to say well; and yet words are not deeds.
  -William Shakespeare"

Sent via Deja.com
http://www.deja.com/

Quote:> Can someone assist me? Sorry if this is too complicated.

> I know the ansi syntax for full outer joins does not work in Oracle.
> (I'm using ODBC BTW)

> Can someone tell me
> 1) the syntax and
> 2) can I retrict the scope of the result set as mentioned below?

You want to take a look at the ODBC outer-join escape sequence {oj } in
your ODBC documentation.  The Oracle ODBC driver will automatically
convert this escape sequence into the appropriate Oracle-specific outer-
join.

Make sure that you're using the latest Oracle ODBC driver if you're
going to be doing a lot of {oj } stuff.  New releases of the driver are
available from OTN <http://otn.oracle.com>

Justin Cave

Sent via Deja.com
http://www.deja.com/