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!
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.