A Complex Query

I’ve writ­ten a lot of SQL, but most of it tends to be pretty sim­ple. Even most of the large queries that I’ve writ­ten aren’t com­plex, they just have a lot of ta­bles and columns to deal with. Probably the most com­pli­cated and in­ter­est­ing query I’ve writ­ten cal­cu­lates dates based on a hi­er­ar­chy of date off­sets.

WITH eventdatesrecursive(eventname, displayname, offsetid, parentoffsetid, eventyear, eventdate) AS
(
  SELECT eventname, displayname, offsetid, parentoffsetid, eventyear, 
         (SELECT api.GetSourceDate(eventname, eventyear) FROM dual) AS eventdate
    FROM (SELECT eventname, displayname, offsetid, parentoffsetid,
                 EXTRACT(YEAR FROM SYSDATE) + column_value - 2 /*Gets last, this, and next year*/AS eventyear
            FROM eventdateoffsets
           CROSS 
            JOIN table(integers(3))
           WHERE parentoffsetid IS NULL
         )
   UNION ALL
  SELECT childdates.eventname, childdates.displayname, childdates.offsetid, 
         childdates.parentoffsetid, eventyear, 
         (SELECT calendar.api.GetEventDate(eventdatesrecursive.eventdate, childdates.offsetid, childdates.eventoffset, eventyear) FROM dual) AS eventdate
    FROM calendar.eventdateoffsets childdates
    JOIN eventdatesrecursive
      ON childdates.parentoffsetid = eventdatesrecursive.offsetid
)
SELECT nvl(overrides.eventname, offsets.eventname) AS eventname, offsets.displayname, 
       nvl(overrides.eventdate, offsets.eventdate) AS eventdate
  FROM eventdatesrecursive offsets
  FULL OUTER
  JOIN eventdateoverrides overrides
    ON offsets.eventname = overrides.eventname
   AND offsets.academicyear = overrides.academicyear;

Recursion, a cross join, a full outer join…there’s a lot of in­ter­est­ing pieces here. Let’s break it down:

SELECT eventname, displayname, offsetid, parentoffsetid,         
  EXTRACT(YEAR FROM SYSDATE) + column_value - 2 AS eventyear /* Gets last, this, next year */    
FROM eventdateoffsets   
CROSS JOIN table(integers(3))   
WHERE parentoffsetid IS NULL

integers is an alias for a sim­ple func­tion that re­turns a pipelined ar­ray of num­bers from 1 up to the pa­ra­me­ter pro­vided:

create or replace function integers(n in number default null) 
  return number_array pipelined
as
begin
  for i in 1 .. nvl(n,0) loop
    pipe row(i);
  end loop;
  return;
end;

So integers(3) re­turns an ar­ray con­tain­ing 1, 2, 3. table is an alias for a func­tion that con­verts the ar­ray to an Oracle table, which al­lows us to join to it. A cross join (aka Cartesian join) matches every row in table A with every row in table B, so no join con­di­tions are nec­es­sary.

eventdateoffsets is the table hold­ing all the event in­for­ma­tion:

event­da­te­off­sets
off­setid	Number (PK)
event­name	Varchar2
par­entoff­setid	Number
eventoff­set	Interval

The par­entoff­setid is the off­setid of the date that this date is based on. The eventoff­set is how much be­fore or af­ter the par­ent date that the event takes place. This al­lows us to set up re­la­tions from our busi­ness rules like “registration al­ways takes place two days af­ter Labor Day” or “students can can­cel courses un­til one week af­ter classes be­gin”. The data looks some­thing like this:

off­setid	event­name	par­entoff­setid	eventoff­set
1	Labor Day	null	null
2	Registration Begins	1	+ 00 08:00
3	Registration Ends	2	+ 03 14:00

Here reg­is­tra­tion be­gins at 8 am on Labor Day and ends three days later at 10 pm. Notice that Labor Day does­n’t have a par­ent; that will be im­por­tant later.

Back to the query:

SELECT eventname, displayname, offsetid, parentoffsetid,         
  EXTRACT(YEAR FROM SYSDATE) + column_value - 2 AS eventyear /* Gets last, this, next year */
FROM eventdateoffsets   
CROSS JOIN table(integers(3))   
WHERE parentoffsetid IS NULL

EXTRACT(YEAR FROM SYSDATE) + column_value - 2 gets the cur­rent year (as of this writ­ing, 2014) and adds the value from the in­te­gers table (1, 2, 3) and sub­tracts 2. In ef­fect, this adds -1, 0, and 1 to the cur­rent year, giv­ing us last year, this year, and next year (as of this writ­ing 2013, 2014, and 2015).

WHERE parentoffsetid IS NULL: re­mem­ber in our ex­am­ple how Labor Day did­n’t have a par­ent? This query is get­ting all the dates with­out a par­ent; I call them “source dates”. These are our foun­da­tion. Some are hol­i­days like Labor Day and Thanksgiving. Others are dates de­ter­mined by Business Rules which haven’t been in­te­grated into this sys­tem. This part of the query gets all the source dates with an eventyear of last year, this year, and next year. Let’s go one level higher:

SELECT eventname, displayname, offsetid, parentoffsetid, eventyear,
  (SELECT api.GetSourceDate(eventname, eventyear) FROM dual) AS eventdate,
  (SELECT api.GetSourceAcademicYear(eventname, eventyear) FROM dual) AS academicyear  
FROM (SELECT eventname, displayname, offsetid, parentoffsetid,               
        EXTRACT(YEAR FROM SYSDATE) + column_value - 2 AS eventyear /* Gets last, this, next year */
      FROM eventdateoffsets         
      CROSS JOIN table(integers(3))         
      WHERE parentoffsetid IS NULL
)

api.GetSourceDate() cal­cu­lates the date for a source date for a given year. It ba­si­cally is a gi­ant CASE that looks some­thing like:

v_StartDate DATE := trunc(to_date(i_Year, 'yyyy'), 'yyyy'); -- First day of the year

CASE p_EventName
WHEN 'Labor Day' THEN
  dbms_scheduler.evaluate_calendar_string
  (
    calendar_string   => 'FREQ=MONTHLY;BYMONTH=SEP;BYDAY=1MON;',
    start_date        => v_StartDate,
    return_date_after => v_StartDate,
    next_run_date     => v_Date
);
…
END CASE;

RETURN v_Date;

api.GetSourceAcademicYear() cal­cu­lates the aca­d­e­mic year (which starts in September when the fall se­mes­ter be­gins) for the event.

Now that the par­ent dates are set, we can de­ter­mine the child dates. Since child dates can be par­ents for fur­ther dates, we need to cal­cu­late the hi­er­ar­chy re­cur­sively:

SELECT eventname, displayname, offsetid, parentoffsetid, eventyear,
  (SELECT api.GetSourceDate(eventname, eventyear) FROM dual) AS eventdate  
FROM (SELECT eventname, displayname, offsetid, parentoffsetid,
        EXTRACT(YEAR FROM SYSDATE) + column_value - 2 /* Gets last, this, next year */AS eventyear
      FROM eventdateoffsets
      CROSS JOIN table(integers(3))
      WHERE parentoffsetid IS NULL
)
UNION ALL
SELECT childdates.eventname, childdates.displayname, childdates.offsetid, 
  childdates.parentoffsetid, eventyear, 
  (SELECT api.GetEventDate(eventdatesrecursive.eventdate, childdates.offsetid, childdates.eventoffset, eventyear) 
   FROM dual) AS eventdate  
FROM eventdateoffsets childdates
JOIN eventdatesrecursive ON childdates.parentoffsetid = eventdatesrecursive.offsetid

This the the ANSI-standard way of do­ing re­cur­sion. You have a query to cal­cu­late your start­ing data, which is then UNIONed to your de­pen­dent data.

The GetEventDate func­tion ba­si­cally adds the child date’s off­set in­ter­val to the par­ent date’s date to get the child date’s date. It also takes Leap Year into ac­count for dates that are set up to be run on a cer­tain day every year (e.g. Independence Day is al­ways July 4).

FUNCTION GetEventDate
(
  i_ParentDate    IN DATE,
  i_EventOffsetID IN NUMBER,
  i_EventOffset   IN INTERVAL DAY TO SECOND,
  i_EventYear     IN t_Year
) RETURN DATE
AS
  k_NewYearsDayEventOffsetID CONSTANT NUMBER := 3;
BEGIN
  RETURN i_ParentDate + i_EventOffset + 
         -- Case statement handles leap year
         CASE
         WHEN api.GetUltimateSource(i_EventOffsetID) = k_NewYearsDayEventOffsetID
              AND to_char(last_day(to_date('02' || i_EventYear, 'mmyyyy')), 'dd') = '29' /* Is Leap Year? */
              AND i_EventOffset > NumToDSInterval(58, 'DAY') /* Is after Feb. 28? */
         THEN 1 
         ELSE 0 
         END;
END GetEventDate;

With the re­cur­sion tucked out of the way in a WITH, the rest of the query is easy.

SELECT nvl(overrides.eventname, offsets.eventname) AS eventname, offsets.displayname,
  nvl(overrides.eventdate, offsets.eventdate) AS eventdate
FROM eventdatesrecursive offsets
FULL OUTER JOIN eventdateoverrides overrides
ON offsets.eventname = overrides.eventname
AND offsets.academicyear = overrides.academicyear;

There is an over­rides table which al­lows any cal­cu­lated date to be over­writ­ten by en­ter­ing a row into the table. I’m do­ing a FULL OUTER JOIN so that the over­ride will cre­ate an event even if it is­n’t set up in eventoffsets.

Phwew! And this is ac­tu­ally a sim­pli­fied ver­sion of the query be­cause there were some busi­ness rules I did­n’t want to get into, just be­cause I wanted to fo­cus more on the SQL. Really though, when you break it down, there aren’t any con­cepts that are too ad­vanced. The dif­fi­culty comes in un­der­stand­ing the query as a whole.