Some problems that ought to be trivial to fix can take an inordinate amount of time to track down and resolve. I recently spent more time than should have been necessary to identify the cause of a problem with some reasonably simple Oracle SQL within an application that I support.
The application manipulates some Railway data, combining Train Crew information with some supplementary Passenger details. It is very typical to find in Train Running and Train Crew data a 7-character field of that defines which day(s) of the week are applicable, with Y and N used to indicate days that are or aren’t applicable. When used in conjunction with separate start and end date fields it is very easy to define a calendar of dates (e.g. Friday only from 1-Dec-2012 to 31-Dec-2012)
The SQL in question needed to find records in the Train Crew data that were applicable to a single date in the supplementary data. Consider the following:
- StartDate = 1-Dec-2012
- EndDate = 31-Dec-2012
- Days = NNNNNYN (Friday Only)
- SupplementaryDate = 10-Feb-2012 (Friday)
The SQL was something like this:
SELECT * FROM TrainData WHERE StartDate <= SupplementaryDate AND EndDate >= SupplementaryDate AND Days LIKE ‘_____Y_’
However, it wasn’t quite that simple because the mask used in the LIKE clause had to be dynamically generated SQL based on SupplementaryDate. The position of the ‘Y’ within the string of underscore characters (the Oracle wildcard for ‘any single character) can be determined by the day number, so with Sunday being the 1st character and Saturday the last, Friday is the 6th character. Using the Oracle TO_CHAR(SupplementaryDate, ‘D’) to return the day number and DECODE to translate that into the necessary string we get:
- DECODE(TO_CHAR(SupplementaryDate+1, ‘D’), ‘1’, ‘Y______’, ‘2’, ‘_Y_____’ etc.
- The ‘D’ parameter of TO_CHAR returns the day of the week (1-7).
- The +1 on SupplementaryDate adjusts for the fact that Monday is the first day of the week in the UK and the masks expects Sunday in the first position.
The code worked fine until one day a customer reported the somewhat unhelpful unexpected results. After several attempts to pinpoint the problem, pouring over data dumps and log files, it finally became apparent that this mask was being generated incorrectly, or more precisely it was one day out, returning ‘______Y’ instead of ‘_____Y_’. Of course this actual string is not exposed in any of the data that was immediately available to me, and most of the records had a variety of other values including ‘YYYYYYY’ or ‘NNYYYYY’ which masked the problem.
Having determined that the wrong mask must be getting generated I had to work out why. Of course, the first day of the week can vary by country, that must surely be causing the problem. According to
Oracle documentation the ‘nlsparam’ argument specifies the language in which month and day names and abbreviations are returned. According to other sources I found on the web this should have resolved my problem, but no amount of fiddling with the SQL gave the correct results, and further investigation suggested that it was the ‘nls_territory’ session setting that needed to be changed. Sure enough, the following commands illustrate the problem but didn’t give me a workable solution:
- ALTER session SET nls_territory=’UNITED KINGDOM’;
- SELECT sysdate, TO_CHAR(sysdate,’D’) FROM dual;
- ALTER session SET nls_territory=’AMERICA’;
- SELECT sysdate, TO_CHAR(sysdate,’D’) FROM dual;
I was unsure how I could reliably execute something like this via an ODBC connection and for it to work with the various ODBC Driver and Oracle database configurations that my customers have. The solution that presented itself was a different approach to calculating the day of the week. By using the ‘J’ parameter instead of ‘D’, the TO_CHAR function calculates the number of days since January 1, 4712 BC, and this number is the same regardless of the national language settings. So now my function has become;
- DECODE(MOD(TO_CHAR(SupplementaryDate+ 1, ‘J’), 7) + 1, 1, ‘Y______’ , 2 , ‘_Y_____’ etc.
Leave a Reply