SQL Query

Posted in 2005

Anirudh Chitnis via DBMonster.com — Mon, 12 Sep 2005 01:47:50 GMT — source: Usenet: comp.databases.informix

Hi, I have a insert query which has got select query inside. Eg: INSERT INTO table_1 (col_1, col_2, col_3, col_4) VALUES (13,'A',0,1 || (SELECT SUBSTRING( NVL(MAX ( col_4 ), 10 ) FROM 2 ) FROM table_1 WHERE col_1 = 13 AND col_2 ='A' ) + 1 ) The select part in the query gives a maximum number for the combination of col_1 & col_2. i.e. col_4 is a running number(serial) depending on col_1 & col_2. My question is when simultaneous users fire this query with the same values for col_1 & col_2, how would the engine behave ? Will I get unique number for col_4 ? I am using IDS 9.30. I have done following testings and did not find any issues :- - Run two queries in batch with "Commited Read" transaction isolation. A unique no. is generated for col_4 in both cases. Any thoughts on this ? -- Message posted via DBMonster.com http://www.dbmonster.com/Uwe/Forums.aspx/informix/200509/1