I was just talking to one of our developers who was having trouble with a view he was accessing. It turned out the view was returning the wrong data, something that was quickly corrected by changing the join columns between two of the three tables accessed to create the view.
I was looking at the original view's data in sql developer and it was displaying okay. I copied the view's SQL into a text editor, made the corrections to the join between two tables, then recreated the view in a separate sqlplus session I had open. I then did a select * from the view and the correct data showed up. All good.
Then I went back into sql developer, refreshed the view saw the correct SQL in the SQL window, then clicked on the data tab to get a tabular view of the data and got the "ORA-01878: specified field not found in datetime or interval" message. I then took the query from the view and ran it in a query window in sqldeveloper and got the same error. Pasted the sql back into sqlplus and no error, just data.
I then disconnected in sqldeveloper and reconnected, but get same result.
The problem is that sqldeveloper does not like the last line which is: AND L.DATE_OUT >= (SYSTIMESTAMP - INTERVAL '12' HOUR); with L.DATE_OUT being a date field. The view is supposed to be showing the last 12 hours worth of records in the table.
The thing is, the data was showing up fine before I recreated the view in sqlplus, and I only changed one line in the view's SQL which was two join fields. Before I changed the join fields, (which have nothing to do with date or time) the same view's data showed up fine in sqldeveloper.
The developer, who is accessing the view through a link from an MSSQL database said that he is now getting the data he needs, so the view is evidently okay otherwise.
I'm using the latest sqldeveloper (Version 19.1.0.094 build 094.2042) and the database is 11.2 XE for 64 bit Windows.
thanks in advance