CODEWITHSUNDEEP
sqloracle-database
SSA_Tech124
SSA_Tech124

Reputation: 599

add condition to return only null values

I have created a query block such that :

select employee_number, name,payroll_name,effective_date,action type
from abc papf,
action ppa
where papf.person_id = ppa.person_id

Now, I have to find employees that have either action_type = 'Q' or have no value in table action. For this I have used the below query :

    select * from 
    (    select employee_number, name,payroll_name,effective_date,action type
        from abc papf,
        action ppa
        where papf.person_id = ppa.person_id
    and action_type = 'Q'
  AND To_char(Nvl(ppa.effective_date, '2019-09-01'), 'YYYY-MM-DD') BETWEEN 
           ('2019-09-01' ) AND ( '2019-12-01' ) 

    UNION
    select employee_number, name,payroll_name,effective_date,action type
        from abc papf,
        action ppa
        where papf.person_id = ppa.person_id
    and action_type IS NULL)

The above query is giving me output :

PERSON_NUMBER   NAME     PAYROLL_NAME         EFFECTIVE_DATE          ACTION_TYPE 
101755          AMIH      Bi weekly           2019-10-10                  Q
101755          AMIH      Bi weekly           2019-10-09                  Q
101755          AMIH      Bi weekly           
1010            SAM       Weekly

I want to add a condition in the action_type = NULL part of the query such that only employees with null action_type is returned and not the ones which have been processed i.e. action_type = 'Q' i.e. employee - 1010 should be returned not the 3rd row 101755 with no action_type as it already has 2 rows with corresponding action_type,effective_Date values

Upvotes: 1

Views: 76

Answers (3)

VBoka
VBoka

Reputation: 9083

You do not need to use UNION, you can do it with one query and subquery.

select employee_number
       , name
       , payroll_name
       , effective_date
       , action_type
from (
    select employee_number
       , name
       , (select count(name) from abc where name = papf.name) mada
       , payroll_name
       , effective_date
       , action_type
    from abc papf
         , action ppa
    where papf.person_id = ppa.person_id
    and (action_type = 'Q'
    and nvl(ppa.effective_date, to_date('2019-09-01', 'YYYY-MM-DD')) BETWEEN 
        to_date('2019-09-01', 'YYYY-MM-DD') AND to_date('2019-12-01', 'YYYY-MM-DD') or action_type is null)
) where mada = 1 or action_type is not null;

Here is the DEMO Hope this will help you!

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

I have to find employees that have either action_type = 'Q' or have no value in table action.

For this, you start with a LEFT JOIN and then filtering:

select employee_number, name, payroll_name, effective_date, actiontype
from abc papf left join
     action ppa
     on papf.person_id = ppa.person_id
where ppa.actiontype = 'Q' or ppa.actiontype is null;

That above assumes that actiontype is not NULL in ppa. Perhaps a more formal version is:

select employee_number, name, payroll_name, effective_date, actiontype
from abc papf left join
     action ppa
     on papf.person_id = ppa.person_id
where ppa.actiontype = 'Q' or ppa.person_id is null;

This checks that there is actually no match.

Upvotes: 0

Popeye
Popeye

Reputation: 35900

You can try the following query which uses analytical function and no union:

Select employee_number, name,payroll_name,effective_date,action type from
(select employee_number, name,payroll_name,effective_date,action type, 
        row_number() over (partition by employee_number order by action_type desc nulls last) as rn
        from abc papf,
        action ppa
        where papf.person_id = ppa.person_id
    and (action_type = 'Q'
  AND To_char(Nvl(ppa.effective_date, '2019-09-01'), 'YYYY-MM-DD') BETWEEN 
           ('2019-09-01' ) AND ( '2019-12-01' ) ) or action_type is null)
Where rn = 1

Cheers!!

Upvotes: 0

Related Questions