Sunday, October 23, 2011

Using Oracle Configured for Different Countries

We load much of the data in our QlikView documents from Oracle databases. A common query technique uses the Oracle Next_Day function to aggregate data into weekly quantities. For example,

Select Next_Day(INC_DATE,2)-7, Count(distinct INCIDENT_NUM) From INC_LOG group by NextDay(INC_DATE,2)-7;

will gather a count of security incidents by week. The Next_Day function in the example should return a Monday date. But, sometimes, when the Oracle database is configured for a European business then the way Oracle numbers days of the week will be different from a database in the U.S. A European database may give Tuesday dates for that example query.

In order to make the SQL commands in a loadscript behave like they do in the U.S. I add a single SQL line to the loadscript following the ODBC Connect string that modifies the Oracle NLS setting temporarily while data is loaded into the document. The line I add after the ODBC connect string is:


Does anyone have a different way of dealing with this issue?

* * *