Different Results on Insert Select than Select
I'm struggling, along with others, where I'm getting different results when doing an Insert select verses just the select.
Lots of joins to get data from various tables. Only want the first record from the uniqfield table so created the tmp_uniqfieldid table with that item fielded, creation_ts thinking it would help matters.....
Here is the query... The insert gets only 44 items where the same select gets the correct 166 items
insert into pack(select a.id, createdate,
createtime, d.user, to_char(b.received_ts, 'YYYYMMDD'),
substr(to_char(b.received_ts, 'HH24MISSFF'),1,8)
FROM uniqfield a, table b, table c, table d,
(Select id, min(creation_ts) creation_ts from uniquefield where fieldid in (select fieldid from pitest.tmp_uniqfieldid)
group by id) uniqfiled_id
where id >' ' and ufield_id.id=a.id and
a.creation_ts = ufield_id.creation_ts and
createdate between 20040714 and 20040731 and
a.fieldid=b.fieldid and b.wk_id =c.wk_id and
trim(c.user_id)=trim(d.user_id))
-----
1) id, createdate, createtime from table uniqfiled a
2) user from table d where useid in table d=userid in table a
3) recvdata, recvdtime from table c need to get wk_id from table b where fieldid in table b =fielded in table a then get wk_id in table b to get recvdate and recvdtime from table c
Software/Hardware used:
Software/Hardware used:
ASKED:
May 3, 2009 11:36 PM
UPDATED:
May 4, 2009 6:44 PM
Answer Wiki:
See discussion below.
To see all answers submitted to the Answer Wiki: View Answer History.
What database are you using ?
Are you running this from an application ?
What is the data type of the field ‘createdate ‘ ?
Have you tried running both, the insert and just the seelct, without this condition: where id >’ ‘ ?
I’m using oracle and have run from Rapid SQL and Toad. Createdate is a float(126).
I am trying now where id<>’ ‘ but will try without the condition to see results…. thanks.
Tried it removing the fieldid>’ ‘ and got same bad results…..
Crazy because based on your questioning the createdate field type, I tried it using the timestamp instead and it worked.
Here is the final query……
insert into pack(select a.id, createdate,
createtime, d.user, to_char(b.received_ts, ‘YYYYMMDD’),
substr(to_char(b.received_ts, ‘HH24MISSFF’),1,8)
FROM uniqfield a, table b, table c, table d,
(Select id, min(creation_ts) creation_ts from uniquefield where fieldid in (select fieldid from pitest.tmp_uniqfieldid)
group by id) uniqfiled_id
where to_char(a.creation_ts, ‘YYYYMMDD’) between 20040714 and 20040731 and
and ufield_id.id=a.id and
a.fieldid=b.fieldid and b.wk_id =c.wk_id
and trim(c.user_id)=trim(d.user_id))
Thanks for the questions that ultimately got the results. First time user here and very happy……
Great !
Thanks for sharing.