Author |
Topic |
bobbychiky
Starting Member
16 Posts |
Posted - 2006-08-20 : 04:25:51
|
---Schema--tag_no - VARCHAR(5)counter_no - VARCHAR(10)order_id - intindicator - intstatus - VARCHAR(2)DATA---{VA001,NULL,1,0,NULL}{VA002,NULL,2,0,NULL}{VA003,NULL,3,0,NULL}{VA004,NULL,3,0,NULL}{VA005,NULL,3,0,NULL}{VA006,NULL,4,2,'NS'}{VA007,NULL,5,0,NULL}UPDATE tagSET counter_no = 'a'WHERE order_id = (select c.order_id from(select a.[order_id] AS order_id, b.[indicator] AS indicator from(select MIN(order_id) AS order_id from tag WHERE counter_no IS NULL) a,(select MIN(indicator) AS indicator from tag WHERE counter_no IS NULL AND status = 'NS') b) cwhere order_id<indicator OR indicator IS NULL)OR indicator = (select c.indicator from(select a.[order_id] AS order_id, b.[indicator] AS indicator from(select MIN(order_id) AS order_id from tag WHERE counter_no IS NULL) a,(select MIN(indicator) AS indicator from tag WHERE counter_no IS NULL AND status = 'NS') b) cwhere order_id>=indicator OR order_id IS NULL) |
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2006-08-20 : 04:29:02
|
your query is quite complex, can you explain the logic when the value should be set to 'A' ???Chirag |
|
|
bobbychiky
Starting Member
16 Posts |
Posted - 2006-08-20 : 05:29:17
|
the query will set the counter_no to a by the follow criteria:it will update the smallest order_id row with counter_no is null.For indicator field, if the row has the indicator smaller or equals the smallest order_id and the row must has the status as 'NS', the query will update the row instead of the row has the smallest order_id. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-08-21 : 03:27:56
|
This is your update query rewritten for easier readingUPDATE tagSET counter_no = 'a'WHERE order_id = ( select c.order_id from ( select a.[order_id] AS order_id, b.[indicator] AS indicator from ( select MIN(order_id) AS order_id from tag WHERE counter_no IS NULL ) a, ( select MIN(indicator) AS indicator from tag WHERE counter_no IS NULL AND status = 'NS' ) b ) c where order_id < indicator OR indicator IS NULL ) OR indicator = ( select c.indicator from ( select a.[order_id] AS order_id, b.[indicator] AS indicator from ( select MIN(order_id) AS order_id from tag WHERE counter_no IS NULL ) a, ( select MIN(indicator) AS indicator from tag WHERE counter_no IS NULL AND status = 'NS' ) b ) c where order_id >= indicator OR order_id IS NULL ) Why do you do a CROSS JOIN with resultset A and resultset B?Peter LarssonHelsingborg, Sweden |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-08-21 : 03:44:46
|
I think this query will doUPDATE TagSET counter_no = 'a'WHERE order_id = ( SELECT TOP 1 t1.order_id FROM Tag t1 LEFT JOIN Tag t2 ON t2.indicator <= t1.indicator AND t2.status = 'NS' WHERE t1.counter_no IS NULL ORDER BY t1.order_id, t1.status DESC ) Peter LarssonHelsingborg, Sweden |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2006-08-21 : 04:00:14
|
quote: Originally posted by PesoWhy do you do a CROSS JOIN with resultset A and resultset B?
I know what you mean: since it all occurs twice, I'd have broken out that bit as a view and probably used subselects in the SELECT clause, but resultset a and b can only contain one row, so c will only contain one row too.Going beyond that, I haven't a clue: the 'explanation' of what the querty does left me less than confident that bobbychiky understands the database.I don't see how your suggestion can be right: it doesn't depend on the value of indicator in the updated row at all. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-08-21 : 04:03:38
|
Yes, C will only contain one row. But that row probably will not exists in reality since cross join of MIN with MIN. Very confused here if his query really works.I ignored his query and I tried my best with his explanation instead to provide an update query that should work according to his explanation.But I am still not convinced either is right.Peter LarssonHelsingborg, Sweden |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-08-21 : 04:07:25
|
You're right.UPDATE TagSET counter_no = 'a'WHERE order_id = ( SELECT TOP 1 ISNULL(t2.order_id, t1.order_id) FROM Tag t1 LEFT JOIN Tag t2 ON t2.indicator <= t1.order_id AND t2.status = 'NS' AND t2.counter_no IS NULL WHERE t1.counter_no IS NULL ORDER BY t2.order_id, t2.status DESC ) Peter LarssonHelsingborg, Sweden |
|
|
bobbychiky
Starting Member
16 Posts |
Posted - 2006-08-21 : 05:03:36
|
sorry, my description is really poor...I give a concept of this function for you...This like a queue.The counter want to reserve the tag_no for serving.For example:COUNTER1 want to get next tag for serving,System will get the smallest order_id tag for COUNTER01,for exception, if on of tag has status 'NS' and has indicator smaller or equals then the smallest order_id. The counter will reserve this tag first.For reserve process, system will update counter_no. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-08-21 : 05:05:58
|
See my last posting.Peter LarssonHelsingborg, Sweden |
|
|
bobbychiky
Starting Member
16 Posts |
Posted - 2006-08-21 : 05:10:45
|
Sorry, one cirteria is missing...If the tag has show_status 'NS',The tag will ignore the order_id. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-08-21 : 05:14:20
|
No, I have that criteria in the JOIN clause.If indicator has precedence, the subquery will take that. But if there is no indicator that has precedence, the subquery will simply take the first null order_id instead.Play around and actually TEST the query. I did. And it works very well on the test data you provided. Maybe you can provide some other test data for further testing?Peter LarssonHelsingborg, Sweden |
|
|
bobbychiky
Starting Member
16 Posts |
Posted - 2006-08-21 : 05:25:13
|
FOR DATA:{VA001,NULL,1,0,NULL}{VA002,NULL,2,4,'NS'}{VA003,NULL,3,0,NULL}{VA004,NULL,4,0,NULL}{VA005,NULL,5,0,NULL}{VA006,NULL,6,0,NULL}{VA007,NULL,6,0,NULL}Try it?expected sequence to update is1,3,2,4,5,6 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-08-21 : 06:17:50
|
This code will satisfy your last sample dataUPDATE TagSET counter_no = 'a'WHERE order_id = ( SELECT TOP 1 ISNULL(t2.order_id, t1.order_id) FROM Tag t1 LEFT JOIN Tag t2 ON t2.indicator <= t1.order_id AND t2.status = 'NS' AND t2.counter_no IS NULL WHERE t1.counter_no IS NULL AND t1.status IS NULL ORDER BY t2.order_id, t1.order_id, t2.status DESC ) For your original sample data, the order is 1, 4, 2, 3, 5.Peter LarssonHelsingborg, Sweden |
|
|
bobbychiky
Starting Member
16 Posts |
Posted - 2006-08-21 : 07:28:58
|
thank you very much~~~you are so strong |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-08-21 : 07:33:41
|
You're welcome.Is everything in order now?Peter LarssonHelsingborg, Sweden |
|
|
bobbychiky
Starting Member
16 Posts |
Posted - 2006-08-21 : 08:02:57
|
yes, it work very well.Thank you~~~ |
|
|
bobbychiky
Starting Member
16 Posts |
Posted - 2006-08-21 : 22:55:28
|
I found that has few problem, My query is using for multi user,if the same query run at same time, they will update the same row??..how can solve this problem?? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-08-22 : 00:35:18
|
1) You can use a transaction.2) You can use a tablelockPeter LarssonHelsingborg, Sweden |
|
|
|