Re: another mental block - Help still wanted Pleeeeease!!!

From: Charlie Edwards <charlie3101_at_hotmail.com>
Date: 5 Nov 2003 07:43:48 -0800
Message-ID: <217ac5a8.0311050743.3a17406b@posting.google.com>

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1067098381.237653_at_yasure>...
> Charlie Edwards wrote:
>
> >create table tclval
> >(v_cat varchar2(2) not null, v_type varchar2(3), v_val number(8,2));
> >insert tclval values ('AV','TLL',150);
> >insert tclval values ('AV',null,100);
> >
> >create table tclnvv
> >(n_id number not null,v_cat varchar2(2) not null, v_type varchar2(3)
> >not null);
> >
> >insert into tclnvv values (2344,'AV','TLL');
> >insert into tclnvv values (2345,'AV','TLP');
> >insert into tclnvv values (2346,'AJ','RDD');
> >insert into tclnvv values (2347,'AJ','RDL');
> >insert into tclnvv values (2349,'AJ','RPN');
> >insert into tclnvv values (2350,'AJ','RPX');
> >insert into tclnvv values (2351,'AJ','RPN');
> >
> >What I would like to do it to retrieve the value for each record in
> >tclnvv using v_cat and v_type to join. However, if there is no exact
> >match then I would like to use the record in tclval where v_cat
> >matches but v_type is null (i.e. v_type is acting as "other").
> >
> >Can anyone help?
> >
> >Thanks
> >
> >CE
> >
> >
> UNION ALL two separate queries. One an inner join, one an outer join
> specifying the NULL condition.

OK, with slightly amended data I get

drop table tclval;
create table tclval
(v_cat varchar2(2) not null, v_type varchar2(3), v_val number(8,2));

insert into tclval values ('AV','TLL',150);
insert into tclval values ('AV',null,100);
insert into tclval values ('AJ','RDL',120);
insert into tclval values ('AJ','RPN',125);

drop table tclnvv;
create table tclnvv
(n_id number not null,v_cat varchar2(2) not null, v_type varchar2(3));

insert into tclnvv values (1111,'AV','TLL');
insert into tclnvv values (2222,'AV','TLP');
insert into tclnvv values (3333,'AV','TQD');
insert into tclnvv values (4444,'AV',null);
insert into tclnvv values (5555,'AJ','RDL');
insert into tclnvv values (6666,'AJ','RPX');
insert into tclnvv values (7777,'AJ','RPN');

My inner join gets me

select n.n_id,
       n.v_cat,
       n.v_type,
       v.v_val
  from tclnvv n,
       tclval v

 where n.v_cat = v.v_cat
   and n.v_type = v.v_type
 order by 1
/

     N_ID V_ V_T V_VAL
--------- -- --- ---------

     1111 AV TLL       150
     5555 AJ RDL       120
     7777 AJ RPN       125

My outer join gets me

select n.n_id,
       n.v_cat,
       n.v_type,
       v.v_val
  from tclnvv n,
       tclval v

 where n.v_cat = v.v_cat(+)
   and n.v_type = v.v_type(+)
 order by 1
/

     N_ID V_ V_T V_VAL
--------- -- --- ---------

     1111 AV TLL       150
     2222 AV TLP
     3333 AV TQD
     4444 AV
     5555 AJ RDL       120
     6666 AJ RPX
     7777 AJ RPN       125

7 rows selected.

So the union all gets me

select n.n_id,
       n.v_cat,
       n.v_type,
       v.v_val
  from tclnvv n,
       tclval v

 where n.v_cat = v.v_cat
   and n.v_type = v.v_type
union all

select n.n_id,
       n.v_cat,
       n.v_type,
       v.v_val
  from tclnvv n,
       tclval v
 where n.v_cat = v.v_cat(+)

   and n.v_type = v.v_type(+)
   and v.v_type is null
 order by 1
/

     N_ID V_ V_T V_VAL
--------- -- --- ---------

     1111 AV TLL       150
     2222 AV TLP
     3333 AV TQD
     4444 AV
     5555 AJ RDL       120
     6666 AJ RPX
     7777 AJ RPN       125

7 rows selected.

But what I need is

     N_ID V_ V_T V_VAL
--------- -- --- ---------

     1111 AV TLL       150
     2222 AV TLP       100
     3333 AV TQD       100
     4444 AV           100
     5555 AJ RDL       120
     6666 AJ RPX       200
     7777 AJ RPN       125

Any help/suggestions gratefully received (I'm getting desperate)

CE Received on Wed Nov 05 2003 - 09:43:48 CST