Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Problem with sequence nextval() in embedded SELECT statement

Status
Not open for further replies.

pennstump

Technical User
Nov 6, 2003
12
0
0
US
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.
 
Hi

You already got the point. First sort it, then add the sequential value.
Code:
[b]insert[/b] [b]into[/b] a
[b]select[/b]
nextval([i]'a_ID_seq'[/i]),0,false,foo.*,[i]'test'[/i]
[b]from[/b] (
  [b]select[/b]
  val,c.code
  [b]from[/b] b
  [b]inner[/b] [b]join[/b] c [b]using[/b] (val)
  [b]order[/b] [b]by[/b] val
) foo

Feherke.
 
Great! Thanks. After some explicit CAST() calls, this worked perfectly.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top