Retrieve single row from a query

Question

I am creating a query to find salary details of an employee with date_to as '31-dec-4712' (Latest). But, If date_to is 31-dec-4712 for two rows for an employee then the one with status 'Approved' should be picked in other cases when only single rows comes then that should be returned as is.

I have created the below query for the salary details. need help with teh above scenario

select distinct PAPF.EMPLOYEE_NUMBER ,
                TO_CHAR (EMP_DOJ (PAPF.PERSON_ID),'DD-MON-YYYY' ) DOJ ,
                TO_CHAR(HR_EMPLOYEE_ORIGINAL_DOJ(PAPF.EMPLOYEE_NUMBER,42) ,'DD-     MON-YYYY' ) ORIGINAL_DOJ,
                PPP.CHANGE_DATE,
                PPP.DATE_TO,
                PPP.PROPOSED_SALARY_N TOTAL_REMUN,
                HR_GENERAL.DECODE_LOOKUP('PER_SAL_PROPOSAL_STATUS',APPROVED) status
from PER_ALL_ASSIGNMENTS_F PAAF,
     PER_ALL_PEOPLE_F PAPF,
     PER_PAY_PROPOSALS PPP
where 1                        = 1
and PAPF.PERSON_ID             = PAAF.PERSON_ID
and PAPF.BUSINESS_GROUP_ID     = 21
and PAPF.CURRENT_EMPLOYEE_FLAG = 'Y'
and papf.employee_number       = '109575'
and :P_DATE1 between PAAF.EFFECTIVE_START_DATE
                 and PAAF.EFFECTIVE_END_DATE
and :P_DATE1 between PAPF.EFFECTIVE_START_DATE
                 and PAPF.EFFECTIVE_END_DATE
and :P_DATE1 between PPP.CHANGE_DATE(+)
                 and NVL(PPP.DATE_TO, HR_GENERAL.END_OF_TIME)
and PPP.ASSIGNMENT_ID(+) = PAAF.ASSIGNMENT_ID
order by TO_NUMBER(PAPF.EMPLOYEE_NUMBER);


Emp_num  DOJ             ORIGINAL_DOJ   CHANGE_DATE      DATE_TO      TOTAL_REMUN  STATUS
109575  01-DEC-2016 24-JUL-2014 01-MAY-19   31-DEC-12   250000    Proposed
109575  01-DEC-2016 24-JUL-2014 01-APR-19   31-DEC-12   100000     Approved

ROWNUM WILL NOT SOLVE IT. i want the condition where salary status is "Approved" only for those where 2 rows comeotherwise i want the query to fetch as is — SSA_Tech124, May 10 '19 at 11:16
Hint: function invocations (e.g. `HR_GENERAL.END_OF_TIME`) in the WHERE clause are a performance killer. Also, NVL evaluates both of the branches every time it's called. Use `COALESCE` instead of `NVL`. — Bob Jarvis - Слава Україні, May 10 '19 at 11:17
Still using outdated and [obsolete `(+)` syntax](https://stackoverflow.com/a/4020794/7998591) for outer joins? It's time to change and switch to [ANSI compliant](https://stackoverflow.com/a/1193722/7998591) **explicit** **Joins** using the **LEFT/OUTER** keywords. — Kaushik Nayak, May 10 '19 at 14:07

score 1 · Answer 1 · answered May 10 '19 at 11:25

If the STATUS takes only two values, "Approved" and "Proposed", you can order by STATUS and fetch the first row. If you have (or in the future you'll have) more statuses and you want to define a priority add a column in the select with a "CASE" that assigns to each status the corresponding priority. Then you order by this column and you fetch the first row....

score 1 · Accepted Answer · answered May 10 '19 at 11:30

You can use conditional ordering for each employee separately, like here:

-- sample rows
with salaries (emp_id, name, salary, date_to, status) as (
    select 1001, 'Orange',  1400, date '4712-12-31', 'Rejected' from dual union all
    select 1001, 'Orange',  1200, date '4712-12-31', 'Approved' from dual union all
    select 1002, 'Red',     2500, date '4712-12-31', 'Approved' from dual union all
    select 1003, 'Blue',    2700, date '4712-12-31', 'Proposed' from dual union all
    select 1004, 'Green',   2200, date '2012-07-31', 'Approved' from dual union all
    select 1005, 'White',   1200, date '4712-12-31', 'Approved' from dual union all
    select 1005, 'White',   1300, date '4712-12-31', 'Rejected' from dual )
-- end of sample data

select emp_id, name, salary, date_to, status
  from (
    select s.*, 
           row_number() over (partition by emp_id 
                              order by case status when 'Approved' then 1 end) rn
      from salaries s
      where date_to = date '4712-12-31')
  where rn = 1

Result:

    EMP_ID NAME       SALARY DATE_TO     STATUS
---------- ------ ---------- ----------- --------
      1001 Orange       1200 4712-12-31  Approved
      1002 Red          2500 4712-12-31  Approved
      1003 Blue         2700 4712-12-31  Proposed
      1005 White        1200 4712-12-31  Approved

order by case status when 'Approved' then 1 end is not working... rn is coming as 1,2 irrespective of that... and the date_to can be any date , in case only singlerow is retirved but in case 2 rows are retrived the row with "approved" is the only one to be picked — SSA_Tech124, May 10 '19 at 11:50

Retrieve single row from a query

2 Answers2