1

I am using SQL developer 3.2.2 to query an Oracle 12 database. I have a select query where I am expecting a certain row to be picked by the query. The results of this query is moved to a global temp table for further processing. But when I query the newly created temp table for the row mentioned above using its key, the query doesn't find the row.

I initially thought that my query had a problem and it wasn't picking up the row at the first place and was debugging the query. But when I ran the query separately on SQL developer and looked for the row by applying a filter on the key column, it shows the row. But when I sort the key column and manually go look for the row in the grid, I don't see the row. I believe it is the same reason why this particular row isn't copied over to the temp table. This is happening to quite a few rows in the database. Has anyone experienced this problem before?

The query is a simple one and has just two columns UserID and LocationID. The query does a union on multiple sub-queries.

select distinct * from (

SELECT distinct UserID, LocationID 
FROM TRANSACTION 
WHERE "Deleted" = 0  and "TransactionType" in ('E1513','E1514')
AND "Date" <= '31-DEC-2016'

UNION 

SELECT distinct UserID, LocationID 
FROM FORMHIS 
WHERE   "FormID" in ('358465','358455')
AND "Date" <= '31-DEC-2016'
)

The output of the above query is missing few rows that I am sure should be in results.

peter.hrasko.sk
  • 4,043
  • 2
  • 19
  • 34
Kris Doe
  • 13
  • 4
  • 1
    Is this really an Oracle temp table? IF so, then you know that you can only see data in a temp table within the same session that updates it. – OldProgrammer Aug 09 '17 at 14:50
  • Yes, I am querying it in the same session. – Kris Doe Aug 09 '17 at 15:09
  • Please show the table definition and query – OldProgrammer Aug 09 '17 at 15:18
  • yeah, we need to see your code. Also, your copy of SQL Developer is really, really old - but that shouldn't impact your issue described here. – thatjeffsmith Aug 09 '17 at 16:04
  • I tried with version 17.2 as well. That didn't help. I'll update the description with the code. – Kris Doe Aug 09 '17 at 19:43
  • I was able to fix the bug by moving the above select queries to two separate temp tables and performing an Union of those two temp tables. Though this technically is the same thing (except for two new unnecessary temp tables), this work around seems to have solved the problem. – Kris Doe Aug 15 '17 at 16:24

0 Answers0