Different Results on Insert Select than Select

35 pts.
Tags:
INSERT SELECT statement
SELECT statement
SQL
SQL query optimization
SQL statements
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

See discussion below.

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    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 >' ' ?
    68,470 pointsBadges:
    report
  • Bem
    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.
    35 pointsBadges:
    report
  • Bem
    Tried it removing the fieldid>' ' and got same bad results.....
    35 pointsBadges:
    report
  • Bem
    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......
    35 pointsBadges:
    report
  • carlosdl
    Great ! Thanks for sharing.
    68,470 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following