In an attempt to be more efficient (and because I apparently have the short-term memory of a goldfish) I've developed some code to go at the end of my procedure for adding new login accounts for T_CUST_LOGIN while tending to the T_NEXT_ID table that I always seem to forget to update. The code is as follows:
UPDATE T_NEXT_IDSET T_NEXT_ID.next_id = (select max(eaddress_no) from T_EADDRESS)where T_NEXT_ID.type='AD' and (select next_id from T_NEXT_ID where type='AD') <> (select max(eaddress_no) from T_EADDRESS)
What do you think, Tessiturians? It should work, right?
BONUS ICHTHYOCENTRIC TRIVIA: The oldest recorded age of a goldfish is 41.
Lisa
--
From: Matt Gonzales To: llindvall@cfl.rr.com Sent: Tuesday, June 08, 2010 1:05 PM Subject: [Tessitura Technical Forum] Clever Code or Foolish Functions? In an attempt to be more efficient (and because I apparently have the short-term memory of a goldfish) I've developed some code to go at the end of my procedure for adding new login accounts for T_CUST_LOGIN while tending to the T_NEXT_ID table that I always seem to forget to update. The code is as follows: UPDATE T_NEXT_IDSET T_NEXT_ID.next_id = (select max(eaddress_no) from T_EADDRESS)where T_NEXT_ID.type='AD' and (select next_id from T_NEXT_ID where type='AD') <> (select max(eaddress_no) from T_EADDRESS) What do you think, Tessiturians? It should work, right? BONUS ICHTHYOCENTRIC TRIVIA: The oldest recorded age of a goldfish is 41. This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
Unknown said: What do you think, Tessiturians? It should work, right?
Why not just use AP_GET_NEXTID to get the id(s)?
Be careful with the Type = 'AD' and max from t_Eaddress since I believe "AD" is used on the T_Address also.
Marty Jones
Database Administrator
Omaha Performing Arts 1200 Douglas Street
Omaha, Nebraska 68102
P 402.661.8469 | F 402.345.0222
Marty.Jones@omahaperformingarts.org
www.omahaperformingarts.org
For tickets, call Ticket Omaha at 402.345.0606
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Matt Gonzales Sent: Tuesday, June 08, 2010 12:06 PM To: Martin A. Jones Subject: [Tessitura Technical Forum] Clever Code or Foolish Functions?
UPDATE T_NEXT_ID SET T_NEXT_ID.next_id = (select max(eaddress_no) from T_EADDRESS) where T_NEXT_ID.type='AD' and (select next_id from T_NEXT_ID where type='AD') <> (select max(eaddress_no) from T_EADDRESS)
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
Excellent. You've both passed the test. Thank you.
Does that SPROC work for all tables, or just the tables defined in T_NEXT_ID? Because I need to know how to get the next ID for the "TX_CUST_KEYWORD" in order that I can insert "customer attributes" based on specific web-user input. I am assuming I need to insert with a unique/next value for the TX_CUST_KEYWORD.keyword_no field, correct?
Thanks!
I'm pretty sure the primary key on tx_cust_keyword is the customer_no, keyword_no, key_value combination. There is no separate id.
I could swear I remembered there being a function for next_id, but now I can't find it. Am I remembering that right?
BTW - this is what I'm trying to do (read my mind here, I know you know what I'm getting at), but obviously I'm doing this wrong...
declare @next_id int
execute @next_id = dbo.AP_GET_NEXTID @type = 'LO', @increment = 1
select @next_id
Annnnnd.. nevermind. Just found the answer. Has the word function in it, but is actually a stored procedure:
execute @next_id = dbo.AP_GET_NEXTID_function @type = 'LO', @increment = 1
Just to confirm: be sure to always use the ap_get_nextid_function; in version 11, there's a change in how nextid's are generated, and this function will be updated and any code using it will continue to function properly.
The version 11 change, in case I've piqued your curiosity: the next_id column will now contain the true "next id", ie, the one that should be assigned to the next object of a particular type. The ap_get_nextid_function will return that value, and then increment it in the t_next_id table, rather than the reverse as done in version 10 and prior.
I’ll also add another tip. If you know the number of rows you’ll be adding ahead of time, it is much more efficient to reserve all new keys at once using the @increment parameter. So if you know you are going to add ten new rows, you can execute like this:
Declare @increment int
Select @increment = COUNT(*) From #myTempTable -- or whatever you need here
execute @next_id = dbo.AP_GET_NEXTID_function @type = 'LO', @increment = 10
Insert dbo.LT_TABLE(id, description, some_other_value)
Select @next_id - 1 ROW_NUMBER() Over (Order by a_unique_identifier_column),
description,
some_other_value
From #myTempTable
The next_id returned is always the first ID in the set. So if you get back a next_id of 100, you have reserved 100-109 for use. Get a count of how many rows, reserve that number of Id’s (keys), then use the Row_number() function to increment the id in a single insert select query.
+Ryan Creps
+Tessitura Network
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Steven BrasSent: Monday, December 12, 2011 5:18 PMTo: Ryan CrepsSubject: Re: [Tessitura Technical Forum] Clever Code or Foolish Functions?
From: Brian Graham <bounce-briangraham8283@tessituranetwork.com>Sent: 12/12/2011 3:23:39 PM