SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Can my statement more simply??
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bobbychiky
Starting Member

16 Posts

Posted - 08/20/2006 :  04:25:51  Show Profile  Reply with Quote
---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  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote
your query is quite complex, can you explain the logic when the value should be set to 'A' ???

Chirag
Go to Top of Page

bobbychiky
Starting Member

16 Posts

Posted - 08/20/2006 :  05:29:17  Show Profile  Reply with Quote
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30108 Posts

Posted - 08/21/2006 :  03:27:56  Show Profile  Visit SwePeso's Homepage  Reply with Quote
This is your update query rewritten for easier reading
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
				)
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30108 Posts

Posted - 08/21/2006 :  03:44:46  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I think this query will do
UPDATE		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
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 08/21/2006 :  04:00:14  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30108 Posts

Posted - 08/21/2006 :  04:03:38  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30108 Posts

Posted - 08/21/2006 :  04:07:25  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

bobbychiky
Starting Member

16 Posts

Posted - 08/21/2006 :  05:03:36  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30108 Posts

Posted - 08/21/2006 :  05:05:58  Show Profile  Visit SwePeso's Homepage  Reply with Quote
See my last posting.

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

bobbychiky
Starting Member

16 Posts

Posted - 08/21/2006 :  05:10:45  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30108 Posts

Posted - 08/21/2006 :  05:14:20  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

bobbychiky
Starting Member

16 Posts

Posted - 08/21/2006 :  05:25:13  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30108 Posts

Posted - 08/21/2006 :  06:17:50  Show Profile  Visit SwePeso's Homepage  Reply with Quote
This code will satisfy your last sample data
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
							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
Go to Top of Page

bobbychiky
Starting Member

16 Posts

Posted - 08/21/2006 :  07:28:58  Show Profile  Reply with Quote
thank you very much~~~you are so strong
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30108 Posts

Posted - 08/21/2006 :  07:33:41  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You're welcome.

Is everything in order now?

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

bobbychiky
Starting Member

16 Posts

Posted - 08/21/2006 :  08:02:57  Show Profile  Reply with Quote
yes, it work very well.
Thank you~~~
Go to Top of Page

bobbychiky
Starting Member

16 Posts

Posted - 08/21/2006 :  22:55:28  Show Profile  Reply with Quote
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??
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30108 Posts

Posted - 08/22/2006 :  00:35:18  Show Profile  Visit SwePeso's Homepage  Reply with Quote
1) You can use a transaction.
2) You can use a tablelock

Peter Larsson
Helsingborg, Sweden
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000