Can my statement more simply?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Can my statement more simply??

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 - 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
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 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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-21 : 03:44:46

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

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-08-21 : 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.

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 Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-21 : 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

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 Larsson
Helsingborg, 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 Larsson
Helsingborg, 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 is
1,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 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

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 Larsson
Helsingborg, 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 tablelock

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources