I have a procedure that contains the following code that works perfectly in ISQL. Essentially, I SET a random number to each record, then SET another value (panel_code) based on that random number. It works great... with one pass through the table.
However, the problem is that when I run it in my application, it fails. The table is updated with a reasonable random number, but the CASE expression drop through to the ELSE every time.
So I tried this...
And guess what? It works perfectly every time. It never drops though to the ELSE. So what's different?
I've tested the input parameters -- I know all the variables have the same value in the code leading up to the UPDATE... so that left me looking for Connection differences.
The only thing different I can find is the Isolation level -- ISQL comes in at Isolation level 0, and the application (where the one-pass code doesn't work) uses Isolation level 1.
So.. is this a reasonable outcome of using Isolation level 1, or do I have a differnt problem.
Mods... please edit the question. I had no idea how to phrase the question!
I'm using 220.127.116.1150
asked 09 Apr '10, 20:50
The issue is that the query is using the original (column) value of random_nbr in the case expression to determine which case sub-expression to use to assign to panel_code.
To do it in one pass, you need to do is compute the new set of random numbers and get your case expression to use the new random number for each row.
UPDATE cheetah.mailing_pool mp SET random_nbr = new_rand, panel_code = ( CASE WHEN new_rand >= @panel_a_lower_threshold and new_rand < panel_b_lower_threshold THEN 'A' WHEN new_rand >= @panel_b_lower_threshold and new_rand < @panel_c_lower_threshold THEN 'B' WHEN new_rand >= @panel_c_lower_threshold and new_rand < @panel_d_lower_threshold THEN'C' WHEN new_rand >= @panel_d_lower_threshold and new_rand <= 1 THEN 'D' ELSE '!!!' -- error, this shouldn't happen END ) FROM ( select dba.f_rand(record_id) as new_rand, mailing_id from cheetah.mailing_pool where mailing_id = @mailing_id ) dt WHERE mp.mailing_id = dt.mailing_id and mp.mailing_id = @mailing_id;
The above may be more (or less) than what you need - i.e. I do not know how many rows in mailing_pool are going to be matched by your predicate
Note: I am assume that your
FWIW: I tested the above using
answered 09 Apr '10, 23:29