The solution is to create another table that contains all of the feecd's
that you are interested in and then do an outer join from this temporary
table to the other two.
> -----Original Message-----
> From: Dennis Taylor [SMTP:ismgr_at_pctc.com]
> Sent: Friday, December 22, 2000 12:53 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: sql problem (hope it's not too easy!)
>
> No, the requirement that's giving me headaches is that, for a given
> customer, I have to show one T2 record for each T1 'FEECODE' type, WHETHER
> OR NOT a T2 record actually exists for that customer for that code. So for
> instance, if I've got codes called 'A', 'B', and 'C', (in T1) and T2
> contains, for cust 1, records with FEECDs 'A' and 'B' but no record for
> 'C', I want to see this:
>
> cust feecd yesno
> ------ -------- -------
> 1 A Y (or whatever)
> 1 B N
> 1 C
>
> I can't get that last line to show up.
>
> At 08:41 AM 12/22/00 -0800, you wrote:
> >Would an outer joing work ? Something like:
> >
> >select t2.custno
> > , t2.feecd
> > , t1.feedesc
> > , decode(t1.feedesc, null, 'YourDefault',null)
> > from t1, t2
> > where t2.feecd = t1.feecode (+)
> >
> >----- Original Message -----
> >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> >Sent: Friday, December 22, 2000 10:46 AM
> >
> >
> >> Because if it is, I'll feel foolish.
> >>
> >> I've been struggling with this one for a bit, and if there's an answer,
> >> it's escaped me.
> >>
> >> Given the following (very simplified) tables:
> >>
> >> T1:
> >>
> >> FEECODE varchar2(10)
> >> FEEDESC varchar2(50)
> >>
> >> constraint T1_PK primary key (FEECODE)
> >>
> >>
> >> T2:
> >>
> >> CUSTNO number(9)
> >> FEECD varchar2(10) (references T1.FEECODE)
> >> YESNO number(1)
> >>
> >> constraint T2_PK primary key (CUSTNO,FEECD)
> >>
> >> For any CUSTNO, FEECD is a value which is *always* found in T1
> (FEECODE).
> >> However, for any CUSTNO, all FEECODE's don't necessarily exist in T2.
> I'd
> >> like to be able to get an sql output, for any given CUSTNO, showing
> >CUSTNO,
> >> FEECODE/FEECD (all possible) ,DESC,YESNO where YESNO is either null or
> >some
> >> default if there isn't an occurrence of CUSTNO+FEECODE in T2.
> >>
> >> So far, everything I've tried results in a list with just the T2 items
> >that
> >> actually exist. I'd like to avoid some monstrous PL/SQL concoction. Is
> >this
> >> possible? (I'm sure it is, and it'll be embarrassingly easy).
> >>
> >> ---
> >> Dennis Taylor
> >> ---
> >> Don't worry about people stealing your ideas. If your ideas
> >> are any good,
> >> you'll have to ram them down people's throats.
> >> --
> >> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> >> --
> >> Author: Dennis Taylor
> >> INET: ismgr_at_pctc.com
> >>
> >> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> >> San Diego, California -- Public Internet access / Mailing Lists
> >> --------------------------------------------------------------------
> >> To REMOVE yourself from this mailing list, send an E-Mail message
> >> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> >> the message BODY, include a line containing: UNSUB ORACLE-L
> >> (or the name of mailing list you want to be removed from). You may
> >> also send the HELP command for other information (like subscribing).
> >
> >--
> >Please see the official ORACLE-L FAQ: http://www.orafaq.com
> >--
> >Author: djordjej
> > INET: djordjej_at_home.com
> >
> >Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> >San Diego, California -- Public Internet access / Mailing Lists
> >--------------------------------------------------------------------
> >To REMOVE yourself from this mailing list, send an E-Mail message
> >to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> >the message BODY, include a line containing: UNSUB ORACLE-L
> >(or the name of mailing list you want to be removed from). You may
> >also send the HELP command for other information (like subscribing).
> >
> >
> ---
> Dennis Taylor
> ---
> Don't worry about people stealing your ideas. If your ideas
> are any good,
> you'll have to ram them down people's throats.
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Dennis Taylor
> INET: ismgr_at_pctc.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Fri Dec 22 2000 - 13:53:14 CST
