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
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