| Author |
 Topic  |
|
|
bobbychiky
Starting Member
16 Posts |
 Posted - 08/20/2006 : 04:25:51
|
---Schema--
tag_no - VARCHAR(5)
counter_no - VARCHAR(10)
order_id - int
indicator - int
status - 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 tag
SET 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)
|
|
|
chiragkhabaria
Flowing Fount of Yak Knowledge
India
1907 Posts |
Posted - 08/20/2006 : 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 - 08/20/2006 : 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
Sweden
29138 Posts |
Posted - 08/21/2006 : 03:27:56
|
This is your update query rewritten for easier readingUPDATE tag
SET 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 Larsson
Helsingborg, Sweden |
Edited by - SwePeso on 08/21/2006 03:30:43 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 08/21/2006 : 03:44:46
|
I think this query will doUPDATE Tag
SET 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 Larsson
Helsingborg, Sweden |
|
|
|
Arnold Fribble
Yak-finder General
United Kingdom
1961 Posts |
Posted - 08/21/2006 : 04:00:14
|
quote:
Originally posted by Peso
Why 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.
|
Edited by - Arnold Fribble on 08/21/2006 04:03:12 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 08/21/2006 : 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 Larsson
Helsingborg, Sweden |
Edited by - SwePeso on 08/21/2006 04:04:32 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 08/21/2006 : 04:07:25
|
You're right.UPDATE Tag
SET 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 Larsson
Helsingborg, Sweden |
Edited by - SwePeso on 08/21/2006 04:17:54 |
|
|
|
bobbychiky
Starting Member
16 Posts |
Posted - 08/21/2006 : 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. |
Edited by - bobbychiky on 08/21/2006 05:05:55 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 08/21/2006 : 05:05:58
|
See my last posting.
Peter Larsson
Helsingborg, Sweden |
|
|
|
bobbychiky
Starting Member
16 Posts |
Posted - 08/21/2006 : 05:10:45
|
Sorry, one cirteria is missing...
If the tag has show_status 'NS',
The tag will ignore the order_id.
|
Edited by - bobbychiky on 08/21/2006 05:11:10 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 08/21/2006 : 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 Larsson
Helsingborg, Sweden |
|
|
|
bobbychiky
Starting Member
16 Posts |
Posted - 08/21/2006 : 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 is
1,3,2,4,5,6 |
Edited by - bobbychiky on 08/21/2006 05:35:24 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 08/21/2006 : 06:17:50
|
This code will satisfy your last sample dataUPDATE Tag
SET 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 Larsson
Helsingborg, Sweden |
Edited by - SwePeso on 08/21/2006 06:24:40 |
|
|
|
bobbychiky
Starting Member
16 Posts |
Posted - 08/21/2006 : 07:28:58
|
| thank you very much~~~you are so strong |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 08/21/2006 : 07:33:41
|
You're welcome.
Is everything in order now?
Peter Larsson
Helsingborg, Sweden |
|
|
|
bobbychiky
Starting Member
16 Posts |
Posted - 08/21/2006 : 08:02:57
|
yes, it work very well.
Thank you~~~ |
|
|
|
bobbychiky
Starting Member
16 Posts |
Posted - 08/21/2006 : 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
Sweden
29138 Posts |
Posted - 08/22/2006 : 00:35:18
|
1) You can use a transaction.
2) You can use a tablelock
Peter Larsson
Helsingborg, Sweden |
|
|
| |
Topic |
|
Can my statement more simply??
