Link to home
Start Free TrialLog in
Avatar of cookiejar
cookiejarFlag for United States of America

asked on

ORACLE 11G If one query return a null, run the second query

I  am using a subquery to derive a column value.  If the first query return a null value, I would like run the second query.
 SELECT
 NAME,
 nvl( ( select p1.assignment_start_date from person_current  p1
       where p1.emp_id  =  '22'
          and p1.assignment_type = 'ACTIVE'
          and p1.assignment_status = 'TEMP'),
           (select  pa.start_date from person_history  pa
                                                                           where emp_id = '22'
                                                                         and pa.dept_id = '14'))) orders_to_date
FROM
    TABLE
 WHERE EIMP_ID =    '22'      

If there is no data in the person_current table for the person, fetch data from the person_history table.

Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

The path you have gone down is interesting but appears to work.  What isn't working for you?

Sample data and expected results would help a lot.

Below is a simple test that shows the logic should work.
select 'Hello',
	nvl((select 'World' from dual),(select 'Bob' from dual)) col1
from dual;

select 'Hello',
	nvl((select null from dual),(select 'Bob' from dual)) col1
from dual;

select 'Hello',
	nvl((select 'World' from dual where 1=2),(select 'Bob' from dual)) col1
from dual;

select 'Hello',
	nvl((select null from dual where 1=2),(select 'Bob' from dual)) col1
from dual;

Open in new window

Here's a quick example using your logic.  You had an extra ')'
drop table person_current purge;
create table person_current( assignment_start_date char(1), emp_id char(2), assignment_type varchar2(10), assignment_status varchar2(10));


drop table person_history purge;
create table person_history( start_date char(1), emp_id char(2), dept_id char(2));

insert into person_current values('a','22','ACTIVE','TEMP');
insert into person_current values('b','23','ACTIVE','TEMP');
insert into person_history values('b','23','14');
commit;

SELECT
 'Hello',
 nvl(
	( select p1.assignment_start_date from person_current  p1
       	where p1.emp_id  =  '22'
          and p1.assignment_type = 'ACTIVE'
          and p1.assignment_status = 'TEMP'),
           (select  pa.start_date from person_history  pa
                                                                           where emp_id = '22'
                                                                         and pa.dept_id = '14')) orders_to_date
FROM
    dual
/

SELECT
 'Hello',
 nvl(
	( select p1.assignment_start_date from person_current  p1
       	where p1.emp_id  =  '23'
          and p1.assignment_type = 'ACTIVE'
          and p1.assignment_status = 'TEMP'),
           (select  pa.start_date from person_history  pa
                                                                           where emp_id = '23'
                                                                         and pa.dept_id = '14')) orders_to_date
FROM
    dual
/

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial