Oracle – for when it was like that when you got there

Main menu

Post navigation

If you can’t do it in PL/SQL, do it in SQL

The tension was palpable on the bridge of The Enterprise. The hulk of the giant Teredation Cruiser filled the View Screen.

With baited breath they awaited the response of the Teredation Captain to the message they had just transmitted.

Suddenly the image on the screen changed from that of the menacing warship to Simon, Lord High Hatter of the Teredations. In a voice which hinted at an unaccustomed uncertainty, he exclaimed,
“What is this PL/SQL of which you speak ?”

OK, so maybe it wasn’t the bridge of the Enterprise so much as in the beer garden at the Nut and Squirrel. The question, however, is pretty much accurate.

In an attempt to distract himself from the sad news that Claude Gnapka had finally left Luton for Walsall, Simon posed the following programming problem :

He needed a SQL query to return the first working day on or after the 23rd of the month, together with the first working day of the following month. He wanted both dates to be returned in the same row.
The catch ? Simon works on Teradata which, owing to a glitch in the Universal Translator ( or something), doesn’t have anything like PL/SQL or T-SQL built in.

We do have something to help us on our way – a table holding all of the UK bank holiday dates. I’ve reproduced this for the 2011 Bank Holidays :

A couple of days later, I received a garbled sub-space message with the following solution ( in Teradation) :

“This solution makes use of Teradata’s proprietary date format (CYYMMDD stored as an integer). It calculates the first working day on or after the 23rd of the month and on or after the last working day of the month. Date testing can be done by hard coding the date in one place (denoted by comment).
“The crux of it is the ‘Work_Days’ derived table – this has only the working days in it and has the next working day adjoined to each row of the interim answerset using the windowed MAX function. This is inclusively inner joined to the full calendar to give you working dates for each date.
“The rest of it is dealing with the difficulty of situations like the end of April 2011:
On 29th April, the Next Working Day (NWD) o nor after the month end is on 3rd May. No problems there.
“The problem comes on 1st May. We’re then looking for the next working day, based on April’s end of month, yet we’re already in May.
NOTE : MED1 is derived as the last day of last month. MED2 is the last day of the current month.”
And the code :

As befitting a race that’s developed Warp Drive technology, the Teradations aren’t completely backward. Note the use of SYS_CALENDAR, something that would appear to be not entirely unlike DUAL in Oracle.

OK, so the Universal Translator has given up the ghost, we’ll just have to roll our sleeves up and do the translation to Oracle manually…

First though, we’re going to make an assumption. There are never more than four consecutive non-working days in the UK. All of our Public Holidays are either on a Friday or a Monday. The only exception is where Christmas Day falls on a Saturday or a Sunday, in which case, the following Monday and Tuesday are Bank Holidays.
Either way, that’s still only four consecutive non-working days.

Secondly, seeing as Simon has seen fit to use a proprietary date format ( these Teredations, coming over here with their fancy date formats…), I’m going to use SQL*Plus to allow variables to be passed into the query…

--
-- Maximum of 4 consecutive working days ( e.g. Bank Holiday, weekend, Bank Holiday
-- or Xmas day falls on a Saturday)
--
set verify off
accept mth prompt 'Enter the month in format MM (e.g. 01 for January): '
accept yr prompt 'Enter the year in format RR (e.g. 11 for 2011): '
SELECT min(workday.day), min(firstday.day)
FROM (
SELECT TO_DATE('23-&mth-&yr', 'DD-MM-RR') as day
FROM dual
UNION ALL
SELECT TO_DATE('24-&mth-&yr', 'DD-MM-RR') as day
FROM dual
UNION ALL
SELECT TO_DATE('25-&mth-&yr', 'DD-MM-RR') as day
FROM dual
UNION ALL
SELECT TO_DATE('26-&mth-&yr', 'DD-MM-RR') as day
FROM dual
UNION ALL
SELECT TO_DATE('27-&mth-&yr', 'DD-MM-RR') as day
FROM dual
) workday,
(
SELECT ADD_MONTHS(TO_DATE('01-&mth-&yr', 'DD-MM-RR'), 1) as day
FROM dual
UNION ALL
SELECT ADD_MONTHS(TO_DATE('02-&mth-&yr', 'DD-MM-RR'), 1) as day
FROM dual
UNION ALL
SELECT ADD_MONTHS(TO_DATE('03-&mth-&yr', 'DD-MM-RR'), 1) as day
FROM dual
UNION ALL
SELECT ADD_MONTHS(TO_DATE('04-&mth-&yr', 'DD-MM-RR'), 1) as day
FROM dual
UNION ALL
SELECT ADD_MONTHS(TO_DATE('05-&mth-&yr', 'DD-MM-RR'), 1) as day
FROM dual
) firstday
WHERE TO_CHAR( workday.day, 'DY') NOT IN ('SAT', 'SUN')
AND TO_CHAR( firstday.day, 'DY') NOT IN ('SAT', 'SUN')
AND workday.day NOT IN ( SELECT bh_date FROM bank_holidays)
AND firstday.day NOT IN (SELECT bh_date FROM bank_holidays)
/

Yes, there’s no join between the in-line views, but we manage to fend off M. Des Cartes with the judicious application of the MIN function to ensure that only a single row is returned.
When we save this in a file called union.sql and run it we get :

6 thoughts on “If you can’t do it in PL/SQL, do it in SQL”

with dates as (select trunc(:p_date) + 21 + level dt -- 23rd of the month and onwards
from dual
connect by level <= 5
union
select last_day(trunc(:p_date)) + level dt -- 1st of next month and onwards
from dual
connect by level last_day(trunc(:p_date)) then dt end)
from dates
where to_char(dt, 'DY', 'nls_date_language=english') not in ('SAT', 'SUN')
and dt not in (select bh_date from bank_holidays)

with dates as (select trunc(sysdate, 'mm') + 21 + level dt -- 23rd of the month and onwards
from dual
connect by level <= 5
union
select last_day(trunc(sysdate)) + level dt -- 1st of next month and onwards
from dual
connect by level last_day(trunc(sysdate)) then dt end)
from dates
where to_char(dt, 'DY', 'nls_date_language=english') not in ('SAT', 'SUN')
and dt not in (select bh_date from bank_holidays)

thanks for your persistence.
Your solution is far more elegant than mine. So what if tree walking ( connect by prior etc) is proprietary to Oracle, they started it with their fancy date formats !
In any case, the Universal Translator has long given up in despair and wandered off in the direction of the pub.
Incidentally, formatting code in wordpress is possible if you use the “sourcecode” “/sourcecode” tags. You need to enclose them in square brackets ( and I can’t drop them into this comment cos it treats them as tags and you don’t see them ! Yes, it took me a while to work that one out as well.

Tree walking is possible in (some versions of) some other rdbms’s, with the advent of Common Table Expressions (at least, I think that’s what they call them!), which Oracle 11g has too (only it’s an advance of the Subquery-factoring we’ve had for a while now!).