Oracle 11g - Is it possible to avoid increasing partition complexity when joining on a one to many table?

user_12274561 0
Viewed 39

I have a query where I want to know a person's current status.

Setup;

create table Person
(
personID int
,forename varchar2(50)
,surname varchar2(50)
);
insert all 
into Person (personID, forename, surname) values (1,'AAA','BBB')
into Person (personID, forename, surname) values (2,'CCC','DDD')
select * from dual;

create table PersonStatus
(
personID int
,fromDate date
,status varchar2(5)
);
insert all
into PersonStatus (personID, fromDate, status) values (1,TO_DATE('2019-01-01','yyyy-mm-dd'),'Q')
into PersonStatus (personID, fromDate, status) values (1,TO_DATE('2019-02-01','yyyy-mm-dd'),'W')
into PersonStatus (personID, fromDate, status) values (1,TO_DATE('2019-03-01','yyyy-mm-dd'),'E')
into PersonStatus (personID, fromDate, status) values (2,TO_DATE('2019-01-01','yyyy-mm-dd'),'R')
into PersonStatus (personID, fromDate, status) values (2,TO_DATE('2019-02-01','yyyy-mm-dd'),'T')
into PersonStatus (personID, fromDate, status) values (2,TO_DATE('2019-03-01','yyyy-mm-dd'),'Y')
select * from dual;

So this query works fine;

select * from 
(
select
p.personID
,p.foreName
,p.surName
,ps.fromDate
,ps.status
,row_number() over (partition by p.personID order by ps.fromDate desc) as rn
from Person p
left outer join PersonStatus ps on p.personID = ps.personID and ps.fromDate <= SYSDATE
) where rn = 1;

  PERSONID FORENAME                                           SURNAME                                            FROMDATE  STATU         RN
---------- -------------------------------------------------- -------------------------------------------------- --------- ----- ----------
         1 AAA                                                BBB                                                01-MAR-19 E              1
         2 CCC                                                DDD                                                01-MAR-19 Y              1

So far so good!

The trouble starts as the report gets more complex. So, say I need to know a list of their issues and their status at the point they have an issue.

Additional setup;

create table PersonIssue
(
issueID int
,personID int
,issueDate date
);
insert all
into PersonIssue (issueID, personID, issueDate) values (1,1,TO_DATE('2019-01-14','yyyy-mm-dd'))
into PersonIssue (issueID, personID, issueDate) values (2,1,TO_DATE('2019-02-14','yyyy-mm-dd'))
into PersonIssue (issueID, personID, issueDate) values (3,1,TO_DATE('2019-03-14','yyyy-mm-dd'))
into PersonIssue (issueID, personID, issueDate) values (4,2,TO_DATE('2019-01-14','yyyy-mm-dd'))
into PersonIssue (issueID, personID, issueDate) values (5,2,TO_DATE('2019-02-14','yyyy-mm-dd'))
into PersonIssue (issueID, personID, issueDate) values (6,2,TO_DATE('2019-03-14','yyyy-mm-dd'))
select * from dual;

New query and results;

select * from
(
select
pi.issueID
,pi.issueDate
,p.personID
,p.foreName
,p.surName
,ps.fromDate
,ps.status
,row_number() over (partition by pi.issueID, p.personID order by ps.fromDate desc) as rn
from Person p
left outer join PersonIssue pi on p.personID = pi.personID
left outer join PersonStatus ps on p.personID = ps.personID and ps.fromDate <= pi.issueDate
order by p.personID, pi.issueDate, ps.fromDate desc
) where rn = 1;


   ISSUEID ISSUEDATE   PERSONID FORENAME                                           SURNAME                                            FROMDATE  STATU         RN
---------- --------- ---------- -------------------------------------------------- -------------------------------------------------- --------- ----- ----------
         1 14-JAN-19          1 AAA                                                BBB                                                01-JAN-19 Q              1
         2 14-FEB-19          1 AAA                                                BBB                                                01-FEB-19 W              1
         3 14-MAR-19          1 AAA                                                BBB                                                01-MAR-19 E              1
         4 14-JAN-19          2 CCC                                                DDD                                                01-JAN-19 R              1
         5 14-FEB-19          2 CCC                                                DDD                                                01-FEB-19 T              1
         6 14-MAR-19          2 CCC                                                DDD                                                01-MAR-19 Y              1

While this works, I have a couple of concerns about this which lead me to think I'm not doing this the right way.

Firstly, is the inequality join in the second query on PersonStatus potentially 'bringing back' lots of records I am not interested in only to be removed later on? Is there a more efficient way?

Secondly, I had to change the partition in the second query in order to get the correct results. Is there a way of doing this that better encapsulates that part of the logic so it does not have to change? I want to know a persons' status on a given date - it feels like I should somehow be able to do this in a more isolated way.

Thanks in advance!

oracle11g oracle
1 Answers

Maybe this will help: {1} Write a VIEW that returns the actual from/to dates for each status. {2} Use INNER JOINs in order to get the required details from your tables.

{1} VIEW

create or replace view status_view
as
select id as personid, status, from_
, case 
    when to_ is null then sysdate  -- latest TO_ date: SYSDATE
    else to_ - 1
  end to_
from (
  select 
    personid id
  , fromdate from_
  , lead( fromdate, 1 ) over ( partition by personid order by fromdate ) to_
  , status status
  from personstatus 
) ;

When SELECTing from the view, you'll get:

select * from status_view ;

  PERSONID STATUS   FROM_       TO_         
         1 Q        01-JAN-19   31-JAN-19   
         1 W        01-FEB-19   28-FEB-19   
         1 E        01-MAR-19   26-OCT-19   
         2 R        01-JAN-19   31-JAN-19   
         2 T        01-FEB-19   28-FEB-19   
         2 Y        01-MAR-19   26-OCT-19   

6 rows selected.

{2} INNER JOINs

select 
  p.personID, p.foreName || ' ' || p.surName as person
, pi.issueID as iid
, ps.from_  as statusfrom
, pi.issuedate     -- issue date in between FROM_ and TO_ (easier to see ...)
, ps.to_ as statusto
, ps.status
from Person p
  join PersonIssue pi 
    on p.personID = pi.personID
  join status_view ps 
    on p.personID = ps.personid
    and pi.issuedate between ps.from_ and ps.to_
order by p.personid, pi.issuedate
;

RESULT

  PERSONID PERSON      IID STATUSFROM   ISSUEDATE   STATUSTO    STATUS   
         1 AAA BBB       1 01-JAN-19    14-JAN-19   31-JAN-19   Q        
         1 AAA BBB       2 01-FEB-19    14-FEB-19   28-FEB-19   W        
         1 AAA BBB       3 01-MAR-19    14-MAR-19   26-OCT-19   E        
         2 CCC DDD       4 01-JAN-19    14-JAN-19   31-JAN-19   R        
         2 CCC DDD       5 01-FEB-19    14-FEB-19   28-FEB-19   T        
         2 CCC DDD       6 01-MAR-19    14-MAR-19   26-OCT-19   Y

You can (of course) write the code of the STATUS_VIEW as an "inline view" into the query - if you want. Tested with Oracle 11c (see dbfiddle) and 18c.

user_81527750
Related