Replacement for MAX statement?

Hi all :
I got a problem here.

The scenario :
The serial key code of a table is auto generate, and currently I'm using MAX to return the latest serial key code. The problem is, if the table grow bigger and access by multiple user at one time ( I think select statement does not have any row or table lock) the MAX key will return the wrong serial key code or even fail at some point. The serial key code is the only unique key in the table.

Can anyone give me some guide or tips on how to resolve this? Or other way of implementing the select statement to avoid usage of MAX?

Thanks in advance.the best way to avoid this is not to return the latest serial key at all

what do you need it for?|||I need to get the serial key code as a foreign key to insert into another table. The Serial key code will server as a customer ID in my case. since no customer ID can be and should be duplicate, I need to get the serial key code.

I had figure out a way, using a select * statement and use a while loop to loop thru the whole result set collection. From there I can get the last serial key code.

I got a question here, if I use this way, will it be less effective than using MAX? Consider I got a table with more than 5000 record.

Thanks.|||On the table you're inserting into, will the SerialKeyCode be unique, or will you have a one-to-many relationship?|||Serial Key Code will be generate automatically by the database and been set to unique with increament by 1 everytime new record is add.|||The problem is, if the table grow bigger and access by multiple user at one time ( I think select statement does not have any row or table lock) the MAX key will return the wrong serial key code or even fail at some point. Thanks in

If you return the maximum serial ID at 14:00 and then again at 14:01 they may indeed be different. I don't see a problem. If you wish to keep an accurate record, then you could implement an insert trigger to store the most recent serial code in another table.|||here's what you do --

insert a row, and let the database automatically assign the key

use the provided database function to retrieve the value of the key that was assigned

in SQL Server, this is the @.@.identity function, in mysql it is mysql_insert_id(), use whatever function your database provides

if your database does not provide such a function, then simply query back the row that you just inserted, not with MAX but with the values of the other columns

then use the value of the key as the foreign key when you insert the related record

for example, if you insert a username/password combination of 'fred','sesame' into a user table, then you instead of selecting MAX or (shudder!) returning the entire table to see the last one, just run this query:

select userid from users where username='fred' and password='sesame'

the automatically incremented userid value that you get back will be what you use to insert the related row in the other table|||Thanks for your suggestion. The problem is that there r no unique key in the table except the auto serial key code assign by the database. I can not use the second method u suggest. About the first suggestion, it's great, but only if I know what kind of statement the databse provide for me to get the number assign. I'm using Informix. It would be great if you can give some hints on the command to use to get the sequence number. Thanks|||ah, informix

look up NEXTVAL and CURRVAL in the manual

the serial number is actually separate from the table, and you use NEXTVAL to get the next value that you insert into the table|||The process is like this :

1)Insert into table A
2)Get the result set from table A (for the latest serial key code)

What you try to said is that I need to use NEXTVAL before step 1 to get the serial key code I need.

I'm using Java. I need to write SQL statement for NEXTVAL or in the Java Code.

Would be appreciate if you can give any hints. TY.|||Insert into tableA values (seq.nextval, column1, column2)
select * from tableA where id = seq.currVal

Replace column value with MAX() query in dataflow before insert

Hi,

We have a dataflow task that imports data from excel to a sql2005 database table. One of the columns is never filled in in the excel source. For updates we can use the lookup transformation to fill in that column, but for new values we need to calculate a new value for it (it's a PK) with MAX(column) +1 and replace the null value in the dataflow with this new value.

Just to be clear:
column id (int)
column name (string)

Based on the 'name' column, we can look up existing ids, and update these in the table. Ids that don't exist yet need to be filled in with the maximum value of the column + 1 (we can't use identity columns) and inserted in the table

Which transformation do I use to replace the value of the id column with this new id?

thanks,

Stephane

Hi Stephane,

You would first need to get the maximum value of the id column into another column (called maxid, for the purposes of this example). You could do this with a script component, or by splitting the flow and using an Aggregate transform,and merging it back. Then, you can then use a derived column transform to replace the value of the column by setting a replace expression such as the following:

ISNULL(id) ? maxid + 1 : id

Mark

|||

Hi Mark,

Thanks for your reply.

Your solution would work if all the data was in the dataflow, which unfortunately it isn't for me (we iterate over excel files with one row per file). We now do a select max() ... at the start of the control flow and store it in a variable. When we detect a new row, we increment the value and use a derived column transform like you suggested as well to put the value in the column.

Thanks,

Stephane

sql