charlie3101_at_hotmail.com (Charlie Edwards) wrote in message news:<217ac5a8.0311050743.3a17406b_at_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
Try the following:
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 v.v_type is null
order by 1
/
Received on Wed Nov 05 2003 - 13:35:48 CST
