This discussion is archived
13 Replies Latest reply: Mar 7, 2013 11:32 PM by 994850 RSS

Strange results with Insert statement having select query

994850 Newbie
Currently Being Moderated
Hi all,
I am facing a strange issue with Insert statement based on a select query having multiple joins.

DB- Oracle 10g

Following is the layout of my query -

Insert into Table X
Select distinct Col1, Col2, Col3, Col4, Function(Col 5) from Table A, B
where trunc(updated_date) > = trunc(sysdate-3)
and join conditions for A, B
Union
Select Col1, Col2, Col3, Col4, Function(Col 5) from Table C, D
trunc(updated_date) > = trunc(sysdate-3)
and join conditions for C, D
Union
.... till 4 unions. all tables are residing in the local Database and not having records more than 50,000.

If I execute above insert in a DBMS job, it results into suppose 50 records where as if I execute the select query it gives 56 records.
We observed following things-
a) no issue with size of tablespace
b) no error while inserting
c) since query takes lot of time so we have not used Cursor and PLSQL block for inserting.
d) this discrepancy in number of records happens frequently but not everytime.
e) examined the records left out from the insert, there we couldn't find any specific pattern.
f) there is no constraint on the table X in which we are trying to insert. Also tables A, B, C....

I went through this thread -SQL insert with select statement having strange results but mainly users are having either DB Links or comparison of literal dates, in my case there is none.

Can somebody explain why is the discrepancy and what is the solution for it.
Or atleast some pointers how to proceed with the analysis.

Edited by: Pramod Verma on Mar 5, 2013 4:59 AM
Updated query and added more details

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points