Re: SQL Query Help, please?

From: Chrysalis <cellis_at_clubi.ie>
Date: Mon, 26 Oct 1998 19:00:09 +0000
Message-ID: <cellis-ya02408000R2610981900090001@news.clubi.ie>

In article <36303fed.580730025_at_firewall.celltech>, jpetts /at\ celltech--dot--co--dot--uk wrote:

> I have two tables (call them Table_A and Table_B for originality :-)
> Table_A is the parent, and Table_B the child. Table_B has a foreign
> key column ProtId referencing ProtId in Table_A.
> snip
> What I need is a query that will retrieve * from Table_A and if
> DictTableCode is 1010, will retrieve ProtParmAlphaValue from Table_B,
> but if DictTableCode is anything else, will retrieve NULL from
> Table_B.

This is a standard outer join, but with an extra condition on the outer-joined table (Table_B)

>snip table definitions and contents
> Should retrieve
>
> ProtId ObjdId AlphaValue NumericValue Status ProtParmAlphaValue
> ====== ====== ========== ============ ====== ==================
> A 1 X 3 OK Parm2
> B 2 Y 4 OK NULL
> C 3 Z 5 OK Parm6
>
>snip

The following will give you what you want:

col ProtParmAlphaValue null "NULL"; -- if required for formatting in SQL*Plus

select ...
 from Table_A A

      ,Table_B B
where  B.ProtId        (+) = A.ProtId

 and B.DictTableCode (+) = '1010'
/

HTH Chrysalis
Chrysalis
--
FABRICATI DIEM, PVNC
("To Protect and to Serve")
Motto of the Night Watch
Terry Pratchett - "Guards, Guards" Received on Mon Oct 26 1998 - 13:00:09 CST