×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Problem with sequence nextval() in embedded SELECT statement

Problem with sequence nextval() in embedded SELECT statement

Problem with sequence nextval() in embedded SELECT statement

(OP)
I have an INSERT statement with an embedded SELECT statement similar to this:

INSERT INTO a SELECT nextval('a_ID_seq') AS ID,0 AS status,false AS void,b.val AS value,c.code AS code,'test' AS createUser FROM b,c WHERE b.val=c.val ORDER BY c.val,b.val

The issue stems from the ORDER BY statement. The point of this query is to sort these values and insert them with the nextval() of the 'a_ID_seq' sequence so they're inserted into Table 'a' in the same order as the ORDER BY. However, the ORDER BY works correctly, but the sequence is called in some order that I can only guess is before the ordering, e.g. it appears that the sequence's next value is assigned to the query results before the ORDER BY is executed, so when I sort the results by the original ORDER BY statement, the IDs are not sequential.

Here's how it should work out:
1,0,false,10,50,'test'
2,0,false,10,60,'test'
3,0,false,20,60,'test'
4,0,false,5,70,'test'

Here's how it does work out (dramatization):
2,0,false,10,50,'test'
3,0,false,10,60,'test'
4,0,false,20,60,'test'
1,0,false,5,70,'test'

The nextval() method is called and assigned before the records are sorted. How can I fix this? Is there a way in pure SQL that I can have the nextval() method called after the records have been sorted, so my IDs are sequential?

Thanks.

RE: Problem with sequence nextval() in embedded SELECT statement

Hi

You already got the point. First sort it, then add the sequential value.

CODE

insert into a
select
nextval('a_ID_seq'),0,false,foo.*,'test'
from (
  select
  val,c.code
  from b
  inner join c using (val)
  order by val
) foo

Feherke.
http://rootshell.be/~feherke/

RE: Problem with sequence nextval() in embedded SELECT statement

(OP)
Great!  Thanks.  After some explicit CAST() calls, this worked perfectly.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close