Select with dual-field empty

[Sonnich]

Hi

I havent touched oracle for some time...

I want to do a select from 2 tables, where the connection between the
tables are fields, but the row migth not exist

e.g.

select * from a, b where a.code=b.code(+), but like this:

select * from a, b
where a.code1=b.code1(+)
and a.code2=b.code2(+)

the point is that both code1 and 2 has to be there.

Any idea how I can do this?

BR
Sonnich

[Martin T.]

Maybe:

select * from a, b
where a.code1=b.code1(+)
and a.code2=b.code2(+)

AND (CODE1 IS NOT NULL AND CODE2 IS NOT NULL)

... maybe it could also be written with the explicit LEFT OUTER JOIN ..
ON syntax ...

cheers,
Martin

[DA Morgan]

WHERE a.code1 IS NOT NULL
AND a.code2 IS NOT NULL

But given that NULL <> NULL I'm not sure you actually have an issue.
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[mgil...@uoguelph.ca]

I may be wrong in this instance, but isn't there a problem with the
original SQL statement because your creating two outer joins on the
same table ( Table 'b' )? Or does this error not occur because they're
different fields.

Cheers,

Mitch

[G Quesnel]

It's perfectly legitimate, as a single outer join based on multiple
columns.
FYI - you could also have ...
where a.code1=b.code1(+)
and a.code2=b.code2
which represent a different business requirement.
hth