Help: decode and joins

We moved from MSSQL6.5 to Oracle8.0.5. I am currently struggling with a simple query that I'm trying to write in Oracle. Here are the parameters in english.

invo table has 1 to many relationship with invm invo table has 1 to many relatioship with invd invd table has 1 to many relationship with ordh

My query must pull invo.ar_invoice_nbr and one other field based on the value of invo.is_manual. The oracle query I wrote looks like this (it crashes SQL worksheet):

select 	invo1.ar_invoice_nbr as invoice,
	decode(invo1.is_manual,
		'Y', invm.description,
		ordh.reference6) as description

from	invo invo1, invo invo2, invm, invd, ordh

where	invo1.ar_invoice_nbr (+) = invm.ar_invoice_nbr
and	invo1.ar_invoice_nbr = invo2.ar_invoice_nbr
and	invo2.ar_invoice_nbr (+) = invd.ar_invoice_nbr
and	invd.order_nbr (+) = ordh.order_nbr

I have two aliases for invo because oracle says I can only "outer join one table" to another.

If this doesn't make any sense, tell me. This is easily done with a CASE statement and LEFT JOINs in MSSQL. I have looked on the internet for explanations, but have found nothing that can solve this without very ugly PL/SQL.

TIA,
Lance

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Mon Aug 16 1999 - 13:03:07 CDT