Forum Discussion
Missing data when loading a view from Oracle
After some debugging, research and several trials, I managed to find the root cause of the problem.
In the view, there is a function converting dates to day of week numbers. If using to_char(sysdate, 'D'), then the output of this function can be different according to where the query is coming from, and what NLS setting is set up in that server. So a different function should be used: TRUNC(sysdate) - TRUNC(sysdate, 'IW'), then you need to add +1 (if you want to have 1 for Monday and 7 for Sunday).
See more details here: https://stackoverflow.com/questions/14517761/using-oracle-sql-how-does-one-output-day-number-of-week-and-day-of-week/71970759#71970759
Hi smoortema,
We wanted to kindly check in to see if everything is working as expected after trying the suggested solution. If there’s anything else we can assist with, please don’t hesitate to ask.
If the issue is resolved, we’d appreciate it if you could mark the helpful reply as Accepted Solution it helps others who might face a similar issue.
Warm regards,
Prasanna Kumar