Re: another mental block

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.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)