Re: Help: decode and joins

On Mon, 16 Aug 1999 18:03:07 GMT, trbobug_at_my-deja.com wrote:

>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.

Lance -

   It sounds as if the invo field values are the parent keys and would be the mandatory value being retrieved by your query. Then, you would want the outer join on the tables with child keys to invo. In that case, your query would look more like:

select   invo.ar_invoice_nbr                       invoice,
            decode(invo.is_manual,
                         'Y', invm.description,
                          ordh.reference6)              description
  from	invo, invm,  invd,  ordh
 where	invo.ar_invoice_nbr = invm.ar_invoice_nbr (+) 
    and	invo.ar_invoice_nbr = invd.ar_invoice_nbr  (+)
    and	invd.order_nbr         = ordh.order_nbr  (+)

  -Lisa Received on Mon Aug 16 1999 - 14:46:24 CDT