cookiejar
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.
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.
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
/
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sample data and expected results would help a lot.
Below is a simple test that shows the logic should work.
Open in new window