CODEWITHSUNDEEP
sqloracle-databaseplsql
user3809240
user3809240

Reputation: 93

Merge two queries without using union

I want to fetch employees who have status in

'A-->Active'
L-->Inactive
T-->terminated

This status has been defined in a table lookup_table. For Active and Inactiev status no other condition is there but for terminated status there is a condition that the term_date can be between today and past 7 days.How can i join these conditions into one query. The query i have written is :-

For active active inactive simply :-

select emp_no , full_name
from people_table pt,
lookup_table lt
where lookup_table_type='ASGN'
and pt.assignment_id=lt.code
and lookup_code in ('A','I')

For terminated :-

select emp_no , full_name
from people_table pt,
lookup_table
where lookup_table_type='ASGN'
and lookup_code in ('T')
and term_date between sysdate and sysdate-7
and pt.system_person_type='EX_EMP'

How can i merge these two queries into one without using union.

Upvotes: 0

Views: 224

Answers (2)

Hogan
Hogan

Reputation: 70513

To "merge" use an OR in your where and you get the same effect.

Like this:

select emp_no , full_name
from people_table pt,
lookup_table lt on pt.assignment_id=lt.code
where lookup_table_type='ASGN'
  and (
       lookup_code in ('A','I')
       OR
       (lookup_code in ('T')
        and term_date between sysdate and sysdate-7
        and pt.system_person_type='EX_EMP')
      )

Upvotes: 1

StephaneM
StephaneM

Reputation: 4899

select emp_no , full_name
from people_table pt,
lookup_table
where lookup_table_type='ASGN'
and (
lookup_code in ('A','I')
or (
and lookup_code in ('T')
and term_date between sysdate and sysdate-7
and pt.system_person_type='EX_EMP' ) )

But I guess you did not show us the full code.

Upvotes: 2

Related Questions