sql - How to copy in field if query returns it blank? -

sql - How to copy in field if query returns it blank? -

i writing query 2 different tables.

table , table b

here query.

select a.out_num, a.timestamp, a.last_name, a.event_type, a.comments, b.name tablea left outer bring together tableb b on a.feed_id = b.id a.out_num = '12345' , a.event_type in ('cause','status')

b.name not null when event_type = xyz else null

i want see when event_type in ('cause','status') want see name field not empty.

second table trying achieve.

thanks

making assumptions info in comments, particularly how match , pick substitute name value; , dummy info think matches yours:

create table tablea(out_num number, equip_name varchar2(5), event_type varchar2(10), comments varchar2(10), timestamp date, feed_id number); create table tableb(id number, name varchar2(10)); alter session set nls_date_format = 'mm/dd/yyyy hh24:mi'; insert tablea values (12345, null, 'abcd', null, to_date('02/11/2013 11:12'), 1); insert tablea values (12345, null, 'abcd', null, to_date('02/11/2013 11:11'), 1); insert tablea values (12345, null, 'abcd', null, to_date('02/11/2013 11:06'), 1); insert tablea values (12345, null, 'abcd', null, to_date('02/11/2013 11:06'), 1); insert tablea values (12345, null, 'sub', null, to_date('02/11/2013 11:11'), 2); insert tablea values (12345, null, 'sub', null, to_date('02/11/2013 11:12'), 2); insert tablea values (12345, null, 'xyz', null, to_date('02/11/2013 11:13'), 3); insert tablea values (12345, null, 'xyz', null, to_date('02/11/2013 11:13'), 3); insert tablea values (12345, null, 'xyz', null, to_date('02/11/2013 11:13'), 3); insert tablea values (12345, null, 'xyz', null, to_date('02/11/2013 11:13'), 3); insert tablea values (12345, null, 'xyz', null, to_date('02/11/2013 11:13'), 3); insert tablea values (12345, null, 'xyz', null, to_date('02/11/2013 11:03'), 3); insert tablea values (12345, null, 'cause', 'apple', to_date('02/11/2013 11:13'), 4); insert tablea values (12345, null, 'cause', 'apple', to_date('02/11/2013 11:13'), 4); insert tablea values (12345, null, 'cause', 'apple', to_date('02/11/2013 11:13'), 4); insert tablea values (12345, null, 'status', 'books', to_date('02/11/2013 11:13'), 5); insert tablea values (12345, null, 'status', 'books', to_date('02/11/2013 11:13'), 5); insert tablea values (12345, null, 'status', 'books', to_date('02/11/2013 11:03'), 5); insert tableb values(3, 'lion');

this gets result:

select * ( select a.out_num, a.timestamp, a.equip_name, a.event_type, a.comments, coalesce(b.name, first_value(b.name) on (partition a.out_num order b.name nulls last)) name tablea left outer bring together tableb b on a.feed_id = b.id a.out_num = '12345' , a.event_type in ('cause', 'status', 'xyz') ) event_type in ('cause', 'status'); out_num timestamp equip_name event_type comments name ---------- ------------------ ---------- ---------- ---------- ---------- 12345 02/11/2013 11:03 status books lion 12345 02/11/2013 11:13 status books lion 12345 02/11/2013 11:13 status books lion 12345 02/11/2013 11:13 cause apple lion 12345 02/11/2013 11:13 cause apple lion 12345 02/11/2013 11:13 cause apple lion

the inner query includes xyz , uses analytic first_value() function pick name if straight matched value null - coalesce may not necessary if there never direct match. (you might need adjust partition by or order by clauses if assumptions wrong). outer query strips out xyz records since don't want those.

if want name value matching record remove filter in inner query.

but you're perhaps more have more 1 non-null record; give 1 matches a.feed_id if exists, or 'first' 1 (alphabetically, ish) out_num if doesn't. order b.id instead, or other column in tableb; ordering in tablea need different solution. if you'll have 1 possible match anyway doesn't matter , can leave out order by, though it's improve have anyway.

if add together more info different out_num:

insert tablea values (12346, null, 'abcd', null, to_date('02/11/2013 11:11'), 1); insert tablea values (12346, null, 'sub', null, to_date('02/11/2013 11:12'), 2); insert tablea values (12346, null, 'xyz', null, to_date('02/11/2013 11:13'), 6); insert tablea values (12346, null, 'cause', 'apple', to_date('02/11/2013 11:14'), 4); insert tablea values (12346, null, 'status', 'books', to_date('02/11/2013 11:15'), 5); insert tableb values(1, 'tiger');

...then - has filter dropped, , i've left out coalesce time - gives same reply 12345, , 12346:

select * ( select a.out_num, a.timestamp, a.equip_name, a.event_type, a.comments, first_value(b.name) on (partition a.out_num order b.name nulls last) name tablea left outer bring together tableb b on a.feed_id = b.id ) out_num = '12346' , event_type in ('cause', 'status'); out_num timestamp equip_name event_type comments name ---------- ------------------ ---------- ---------- ---------- ---------- 12346 02/11/2013 11:14 cause apple tiger 12346 02/11/2013 11:15 status books tiger

... tiger linked abcd, not xyz.

sql oracle oracle11g