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,
Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.
If the response addressed your query, kindly mark it as Accepted Solution and click Yes if you found it helpful — this will benefit others in the community as well.
Best regards,
Prasanna Kumar