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'),
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
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