Hi,

I would like to do the following select statement:

select iasmatfms.material,
iasmatfms.statmatnum,
iasmatx.stext,
iasmatx.ltext
from iasmatx right outer join iasmatfms on iasmatfms.material = iasmatx.material

to have all items listed in both databases, but it doesn't work. I always get an error "Command not properly ended".

What is the reason?


------------------
--
Rgds,
Norbert

What version is your Oracle Database?
"Right Outer Join" syntax is supported in
Oracle 9i, but not Oracle8i or earlier.

Try this:

select iasmatfms.material,
iasmatfms.statmatnum,
iasmatx.stext,
iasmatx.ltext
from iasmatx,iasmatfms
where iasmatfms.material = iasmatx.material(+)


The (+) on the iasmatx.material column will
preserve that column as a null if no match
is found for iasmatfms.material. You can
simply place the (+) on the other column if
that is the one that should be preserved.

Hi,

It is Oracle 8i, but it works with the (+).
Great, and many thanks.

[quote]Originally posted by foldenm:
What version is your Oracle Database?
"Right Outer Join" syntax is supported in
Oracle 9i, but not Oracle8i or earlier.

Try this:

select iasmatfms.material,
iasmatfms.statmatnum,
iasmatx.stext,
iasmatx.ltext
from iasmatx,iasmatfms
where iasmatfms.material = iasmatx.material(+)


The (+) on the iasmatx.material column will
preserve that column as a null if no match
is found for iasmatfms.material. You can
simply place the (+) on the other column if
that is the one that should be preserved.

[/quote]



------------------
--
Rgds,
Norbert