Sunday, October 23, 2011
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:
SQL ALTER SESSION SET NLS_TERRITORY='America';
Does anyone have a different way of dealing with this issue?
* * *