seenall
asked on
Oracle Date Query - different Results depending on Host OS running DB Connection
Hello
After some research (probably from another EE page) I managed to construct a query that shows the dates of the first and last day of the week week for the past three weeks and current week
The working week in my scenario is Sunday to Sunday.
The query Q1 works exactly as required when executed via SQL*Plus on a UNIX client
But in windows (the final destination of the impementation) - any client connection (TOAD, SQL*Plus SRSS) the dates are shifted to the right by one.
Okay my obvious fix is to adjust lines 2 and 3 to report a day behind to pull the result set as expected (Q2) - but this leads to inconsistent developments and integrations
Has anyone come across this before? Did they get an idea why this happens?
Thanks in advance
After some research (probably from another EE page) I managed to construct a query that shows the dates of the first and last day of the week week for the past three weeks and current week
The working week in my scenario is Sunday to Sunday.
The query Q1 works exactly as required when executed via SQL*Plus on a UNIX client
But in windows (the final destination of the impementation) - any client connection (TOAD, SQL*Plus SRSS) the dates are shifted to the right by one.
Okay my obvious fix is to adjust lines 2 and 3 to report a day behind to pull the result set as expected (Q2) - but this leads to inconsistent developments and integrations
Has anyone come across this before? Did they get an idea why this happens?
Thanks in advance
Q1
select rank() over (partition by period order by last_sunday) weekago, last_sunday, next_saturday from
(select 'week' period, trunc(sysdate, 'day') + -7 - (p.pivot*7) last_sunday
, trunc(sysdate, 'day') + -1 - (p.pivot*7) next_saturday
from dual d
, (select rownum pivot
from all_objects
where rownum < 3) p
union
select 'week' period, trunc(sysdate, 'day') + 7 - (p.pivot*7) last_sunday
, trunc(sysdate, 'day') + 13 - (p.pivot*7) next_saturday
from dual d
, (select rownum pivot
from all_objects
where rownum < 3) p
) order by 1 asc
/
RESULTS on SQL*Plus via UNIX - Results as I would expect them
WEEKAGO LAST_SUND NEXT_SATU
---------- --------- ---------
1 24-JAN-10 30-JAN-10
2 31-JAN-10 06-FEB-10
3 07-FEB-10 13-FEB-10
4 14-FEB-10 20-FEB-10
RESULTS from SQL*Plus via Windows (and SSRS and Toad)
shifts one day to right
WEEKAGO LAST_SUND NEXT_SATU
---------- --------- ---------
1 25-JAN-10 31-JAN-10
2 01-FEB-10 07-FEB-10
3 08-FEB-10 14-FEB-10
4 15-FEB-10 21-FEB-10
Resolved in windows clients with the following (lines 2&3 and 9&10 Adjusted)
Q2
select rank() over (partition by period order by last_sunday) weekago, last_sunday, next_saturday from
(select 'week' period, trunc(sysdate, 'day') + -8 - (p.pivot*7) last_sunday
, trunc(sysdate, 'day') + -2 - (p.pivot*7) next_saturday
from dual d
, (select rownum pivot
from all_objects
where rownum < 3) p
union
select 'week' period, trunc(sysdate, 'day') + 6 - (p.pivot*7) last_sunday
, trunc(sysdate, 'day') + 12 - (p.pivot*7) next_saturday
from dual d
, (select rownum pivot
from all_objects
where rownum < 3) p
) order by 1 asc
/
WEEKAGO LAST_SUND NEXT_SATU
---------- --------- ---------
1 24-JAN-10 30-JAN-10
2 31-JAN-10 06-FEB-10
3 07-FEB-10 13-FEB-10
4 14-FEB-10 20-FEB-10
is the SQL Server zone correct? Looks like you are asking about Oracle
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
sdstuber : please see comments I make below for gatorvip and if you have any thoughts on the underlying issue I will be grateful
gatorvip: Thanks - after switching some of the NLS parameters at session level I deduced our UNIX server is configured with AMERICA (specifically NLS_Territory) and the Windows Server came in with UNITED KINGDOM
Changing the Windows Server to AMERICA for NLS_Territory corrected the issue and Q1 returned identical results to that returned from the UNIX Server
Question remains - is it even possible to over-ride the NLS parameters at session level from an SRSS connection? Attempted to find a setting in the Data Source - Also Tried (from SRSS Tools Options > Environment -> International Settings but there is only 2 options here - English or "Same as Microsoft Windows"
Now I can go about changing the global environment settings on both Servers to align - but this might create me a headache as both are production servers hauling out more reports than I care to mention - I mean to say I won't be employed long if the statistics in reports fall out of wack because the clause trunc(sysdate) is everywhere amongst the report
I also Asked MR.G (google) for thoughts on SRSS and NLS and he came up blank too. If there is any other way to change NLS settings at session level created by SRSS that would be ideal
Thanks again
gatorvip: Thanks - after switching some of the NLS parameters at session level I deduced our UNIX server is configured with AMERICA (specifically NLS_Territory) and the Windows Server came in with UNITED KINGDOM
Changing the Windows Server to AMERICA for NLS_Territory corrected the issue and Q1 returned identical results to that returned from the UNIX Server
Question remains - is it even possible to over-ride the NLS parameters at session level from an SRSS connection? Attempted to find a setting in the Data Source - Also Tried (from SRSS Tools Options > Environment -> International Settings but there is only 2 options here - English or "Same as Microsoft Windows"
Now I can go about changing the global environment settings on both Servers to align - but this might create me a headache as both are production servers hauling out more reports than I care to mention - I mean to say I won't be employed long if the statistics in reports fall out of wack because the clause trunc(sysdate) is everywhere amongst the report
I also Asked MR.G (google) for thoughts on SRSS and NLS and he came up blank too. If there is any other way to change NLS settings at session level created by SRSS that would be ideal
Thanks again
seenall, it still looks like you only want an Oracle answer. If so I will rezone the question for you. If you do want an answer for SQL Server as well, I will leave it alone.
ASKER
sdstuber - Your input is really appreciated - the question is how to make an SRSS session adjust the NLS session parameters when running a report ?
Any thoughts ?
Any thoughts ?
does that mean you want an Oracle answer only or not?
I keep asking that same question repeatedly because I want to know how to best answer your question.
If I give you an answer that will only work in Oracle but you need to use SQL Server too, then that's not helpful.
Same as if I give you a SQL Server answer if you need Oracle.
If you need both, please say so.
If you need only one, please say so.
Thanks
If I give you an answer that will only work in Oracle but you need to use SQL Server too, then that's not helpful.
Same as if I give you a SQL Server answer if you need Oracle.
If you need both, please say so.
If you need only one, please say so.
Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
gatorvip: Thanks for the response
Environment Clarification
DB Oracle
Host Sun UNIX (version I am not familiar)
BI Server 1 - Linux Debian - using shell scripts , SQL Plus and PERL to email results
BI Server 2 - Windows Server 2003 - with SQL Server Reporting Services using Microsoft end to end stack (OS,Internet Service, Report Server, Report Manager)
Both BI Servers are connecting to the same DB Host (Sun / Oracle 10g) over Oracle tnsnames
The question (identified through this process) relates to the mix of environments and the different result set being returned from the Oracle SQL Query.
The function you kindly crafted goes a long way and I will certainly give it serious consideration when weighing up the options
Environment Clarification
DB Oracle
Host Sun UNIX (version I am not familiar)
BI Server 1 - Linux Debian - using shell scripts , SQL Plus and PERL to email results
BI Server 2 - Windows Server 2003 - with SQL Server Reporting Services using Microsoft end to end stack (OS,Internet Service, Report Server, Report Manager)
Both BI Servers are connecting to the same DB Host (Sun / Oracle 10g) over Oracle tnsnames
The question (identified through this process) relates to the mix of environments and the different result set being returned from the Oracle SQL Query.
The function you kindly crafted goes a long way and I will certainly give it serious consideration when weighing up the options
ASKER
sdstuber: - please see previous post - The question is not related SQL Server Database, but to the SQL Server Reporting Services, and how to over-ride session parameters.
All responses much appreciated
All responses much appreciated
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
So as a summary
It seems there does not appear to be way to over-ride DB connection session parameters from SSRS when connecting to Oracle DB over TNSNAMES
In order to standardise the query across environments a Database Server side function is necessary.
This is great - thanks. It is a shame that the logic of the report in its entirety cannot be written into a single code library and deployment. A standard procedure with most reporting applications I guess.
Thanks for all input
It seems there does not appear to be way to over-ride DB connection session parameters from SSRS when connecting to Oracle DB over TNSNAMES
In order to standardise the query across environments a Database Server side function is necessary.
This is great - thanks. It is a shame that the logic of the report in its entirety cannot be written into a single code library and deployment. A standard procedure with most reporting applications I guess.
Thanks for all input
My last query should be consistent from any client and doesn't require a function to be written on the server side.
And, for this query there doesn't appear to be any "session" information required. Or maybe I'm misunderstanding your requirement.
It seems like pretty basic sql
And, for this query there doesn't appear to be any "session" information required. Or maybe I'm misunderstanding your requirement.
It seems like pretty basic sql
ASKER
Thank you
for patience
and understanding
This works a treat ! Wish I had awarded more points when posing the question - my bad
Best regards
for patience
and understanding
This works a treat ! Wish I had awarded more points when posing the question - my bad
Best regards
glad we could help,
also you can change points for a question until you close it.
After you close it a moderator or advisor can change the points too. I "could" but since I'm participating in the question it would be inappropriate for me to do so.
Also, it's unnecessary unless gatorvip disagrees.
also you can change points for a question until you close it.
After you close it a moderator or advisor can change the points too. I "could" but since I'm participating in the question it would be inappropriate for me to do so.
Also, it's unnecessary unless gatorvip disagrees.
Re: #26584868
Cool, the next_day() definitely cleans it up. I thought that function was also depending on the environment so I didn't use it, but I checked the docs and it does work.
Cool, the next_day() definitely cleans it up. I thought that function was also depending on the environment so I didn't use it, but I checked the docs and it does work.
ASKER
Update
I had to adjust the query slightly because when it was run on a sunday, the dates pulled back returned a start and end period with 14 days between. Only on Sundays. Unfortunately this was the day the scheduled report was due to run
So I introduced a check on the 'Day' parameter of the date, checked if it was Sunday and set the date to work from to Sysdate+1
Example below
I had to adjust the query slightly because when it was run on a sunday, the dates pulled back returned a start and end period with 14 days between. Only on Sundays. Unfortunately this was the day the scheduled report was due to run
So I introduced a check on the 'Day' parameter of the date, checked if it was Sunday and set the date to work from to Sysdate+1
Example below
select * from ( SELECT 5 - n weekago,
NEXT_DAY(d - 8 - (n * 7), 'Sunday') last_sunday,
NEXT_DAY(d - 1 - (n * 7), 'Saturday') next_saturday
FROM (select 5 - level n
, trunc(case when rtrim(to_char(sysdate,'Day')) = 'Sunday' then sysdate+1 else sysdate end) d
FROM DUAL
CONNECT BY LEVEL <= 5)
ORDER BY 5 - n )
ASKER
FROM (Incorrect) The working week in my scenario is Sunday to Sunday.
TO (Correct) The working week in my scenario is Sunday to Saturday.