Connect By query with recurring hierarchical checks

Question

I have written the following query and unfortunately it does not work. All I am trying to achieve is that the dates that I select with each row is the effective date of the customer relationship.

The ci_per_per table contains the following columns:

per_id1, per_id2, start_dt, end_dt

per_id1 is the parent, per_id2 is the child.

start_dt and end_dt define the duration of the relationship

SELECT
  CONNECT_BY_ROOT per_id2                                                           AS per_id2,
  per_id1,
  (CASE WHEN start_dt < (PRIOR start_dt1)
            THEN (PRIOR start_dt1) 
            ELSE start_dt 
        END) AS start_dt1,
  (CASE WHEN end_dt > (PRIOR end_dt1) 
             THEN (PRIOR end_dt1) 
             ELSE end_dt 
        END)         AS end_dt1,
  level                                                                             AS "PER_LEVEL"
  FROM ci_per_per
  CONNECT BY NOCYCLE PRIOR per_id1 = per_id2

The error I am getting for the sql is: END_DT1: Invalid identifier.

Is there a way to achieve this functionality in oracle?

Example scenario:

per_id1 | per_id2 |   start_dt  |   end_dt
-------------------------------------------
B       |   A     |  01-01-2011 | 01-01-2011
C       |   B     |  01-01-2010 | 01-01-2010
E       |   B     |  01-01-2011 | 01-01-2014
D       |   C     |  01-01-2009 | 01-01-2015

now I want all the records to be having 01-01-2011 as thier start date. However, if the hierarchy is reversed(meaning exchange per_id_1 and Per_id2 values), then the records should be displayed as they are.

Note that for the above example, we need to run it with the start with clause with per_id2='A' condition. Also, understand that the results might change if you run the query with the per_id2='B'

Thus, when I start with per_id2='B' the result should make all the records to be 01-01-2010 and not 01-01-2011

now with the end date, it should be 01-01-2011 for all records except the one with E and the one with A. where the end date will be null (assuming we start with A).

I suspect you'll have to use recursive sub-query factoring (a recursive CTE) in order to achieve this. — Ben, Sep 03 '13 at 13:11
can you please elaborate with some details? thanks in advance! — MozenRath, Sep 03 '13 at 13:12
Are you trying to get the lowest start and end dates within the hierarchy? — Joe, Sep 03 '13 at 13:17
Still not totally clear what you want. What if, in your example, the second row (C/B) was 01-01/2013? Do you want the highest value *anywhere* in the hierarchy, or the highest below the current level? — Joe, Sep 03 '13 at 15:58
@Joe, Thanks for asking, I want the highest value below the current level. — MozenRath, Sep 03 '13 at 17:51

Joe · Answer 1 · 2013-09-03T19:34:44.713

1

A combination of an Oracle Hierarchy and CTE will produce the result you want:

WITH cte AS 
(SELECT 
  per_id1,
  max(connect_by_root start_dt) as max_start,
  min(connect_by_root end_dt) as min_end
FROM 
  ci_per_per
CONNECT BY 
  NOCYCLE PRIOR per_id1 = per_id2
GROUP BY 
  per_id1)
SELECT
  ci_per_per.per_id1,
  ci_per_per.per_id2,
  cte.max_start,
  cte.min_end
FROM
  cte
  join ci_per_per on cte.per_id1=ci_per_per.per_id1

See SQLFiddle here Your example is fairly simple, so I can't be certain that this logic will work for all of your test cases.

EDIT updated for lowest end_dt.

edited Sep 03 '13 at 19:34

answered Sep 03 '13 at 18:02

Joe

6,767
1
16
29

Nicely done, but If you see the select columns from my query, then you will understand that there is a connect by root in it. also, there can be multiple children for one parent for different date ranges(however, only one relationship would be active at any particular time) – MozenRath Sep 03 '13 at 18:30
Can you update question with the additional scenarios and what the expected result should be? – Joe Sep 03 '13 at 18:40
Updated answer for `end_dt`. – Joe Sep 03 '13 at 19:37
Using `start with per_id1='A'` doesn't make sense since A is at the top of the hierarchy -- no rows will be produced. See here: http://www.sqlfiddle.com/#!4/56f7d/10/0 – Joe Sep 03 '13 at 19:48
actually A is at the bottom of the hierarchy. and I have mentioned `per_id2='A'`. Maybe this Fiddle might help you: http://www.sqlfiddle.com/#!4/56f7d/15 – MozenRath Sep 03 '13 at 20:17

MozenRath · Accepted Answer · 2013-09-11T06:19:32.697

I have been trying to work out the solution to this problem and have found a solution.

Please correct me if you think there is any issue with this below SQL:

select *
  from (SELECT root as per_id2,
               per_id1,
               PER_LEVEL,
               (case
                 when max(connect_by_root start_dt) >= max(start_dt) then
                  max(connect_by_root start_dt)
                 else
                  max(start_dt)
               end) as max_start,
               (case
                 when min(connect_by_root end_dt) <= min(end_dt) then
                  min(connect_by_root end_dt)
                 else
                  min(end_dt)
               end) AS min_end
          FROM (SELECT connect_by_root per_id2 AS root,
                       per_id1,
                       per_id2,
                       level as per_level,
                       (case
                         when connect_by_root start_dt >= start_dt then
                          connect_by_root start_dt
                         else
                          start_dt
                       end) as start_dt,
                       (case
                         when connect_by_root end_dt <= end_dt then
                          connect_by_root end_dt
                         else
                          end_dt
                       end) as end_dt
                  FROM ci_per_per
                CONNECT BY NOCYCLE PRIOR per_id1 = per_id2)
        CONNECT BY NOCYCLE PRIOR per_id1 = per_id2
               and PRIOR root = root
         GROUP BY per_id1, per_id2, root, per_level)
 where max_start <= min_end

Here's the fiddle for the same

Connect By query with recurring hierarchical checks

2 Answers2