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! Thanks! I'm using 9.0.2.3850 asked 09 Apr '10, 20:50 Ron Hiner |
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. For example: 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 Mark Culp Thanks Mark -- you wrote "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." Does that behavior change with Isolation level? Is my diagnosis correct that it's the isolation level that determines if either the original value or the new value of random_nbr is used to process the CASE sub-expression? @Ron: I do not believe that isolation level has any direct effect on the behaviour of how the expressions are evaluated. I tried my test (see bottom of post) with different isolation levels and did not see any difference in behaviour. I'm not sure why you saw a difference? Perhaps you ran your statement twice? Note that you need to be careful when using dbisql - depending on settings, it will sometimes run your statement twice under-the-covers. FWIW I used dbisqlc during my tests. |