Need help to build a PL/SQL Query

Question

Hi,

I'm having scenario to which a sql query need to be built. I tried to come up with a efficient query, but could not find a clear way of doing this. My scenario is as follows:

I'm having TABLE_A and TABLE_B ,where FIELD_AB will definitely be a filed of TABLE_A, however, there can be exist FIELD_AB in TABLE_B.

I need to retrieve value for FIELD_AB, from TABLE_B if such field exist, if it is not, then retrieve value for FIELD_AB from TABLE_A.

I'm looking for a single query to retrieve the value of FIELD_AB, and according to my knowledge CASE statement can be used to accomplish this, but not clear a better way of using it.

EDIT:

Please do not misunderstood question. What I mean by "FIELD_AB can be exist" is that there is a possibility of FIELD_AB itself does not exist in the TABLE_B, not a value for FIELD_AB Any help appreciated

Thank You

Answer 1

You probably need to use an outer join to link the two tables:

select a.id
       , case when b.col_ab is null then a.col_ab
              else b.col_ab end as ab
from table_b b
     left outer join table_a a
     on ( b.id = a.id )
/

Oracle has some alternative ways of testing for NULL. A simpler, if non-standard, way of testing for AB would be:

nvl2(b.col_ab, b.col_ab, a.col_ab) as ab

This is logically identical to the more verbose CASE() statement.

Answer 2

create table table_b ( field_ab int not null, value varchar(20) not null )

create table table_a ( field_ab int not null, value varchar(20) not null )

insert into table_a values( 1, '1 from a')
insert into table_a values( 2, '2 from a')
insert into table_a values( 3, '3 from a')

insert into table_b values( 2, '2 from b')

-- result is '2 from b'
select
    case when b.field_ab is null then a.value
    else b.value
    end
from table_a a left outer join table_b b on a.field_ab = b.field_ab
where a.field_ab = 2

-- result is '1 from a'
select
    case when b.field_ab is null then a.value
    else b.value
    end
from table_a a left outer join table_b b on a.field_ab = b.field_ab
where a.field_ab = 1