CASE usage - SQL Server Forums

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

Transact-SQL (2000)

CASE usage

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-05-18 : 08:51:10

Paul writes "I have been through dozens of web sites trying to find the answer to the following question and your examples seem to come the closest, although not quite.

My query is as follows:

SELECT well,
CASE
WHEN well = '2490010352320100541' THEN 1
WHEN well = '2490010352317700541' THEN 2
WHEN well = '2490010352314000541' THEN 3
WHEN well = '2490010352319500541' THEN 4
WHEN well = '2490010352317100541' THEN 5
WHEN well = '2490010352203900541' THEN 6
WHEN well = '2490010352318700541' THEN 7
WHEN well = '2490010352304200541' THEN 8
WHEN well = '2490010352304100541' THEN 9
WHEN well = '2490010352313900541' THEN 10
WHEN well = '2490010352304000541' THEN 11
WHEN well = '2490010352290600541' THEN 12
WHEN well = '2490010352307000541' THEN 13
WHEN well = '2490010352325200541' THEN 14
WHEN well = '2490010352306800541' THEN 15
WHEN well = '2490010352306700541' THEN 16
WHEN well = '2490010352266600541' THEN 17
WHEN well = '2490010352266800541' THEN 18
WHEN well = '2490010352266400541' THEN 19
WHEN well = '2490010352311900541' THEN 20
END AS SORT_COL
FROM XY
WHERE
well = '2490010352320100541' OR
well = '2490010352317700541' OR
well = '2490010352314000541' OR
well = '2490010352319500541' OR
well = '2490010352317100541' OR
well = '2490010352203900541' OR
well = '2490010352318700541' OR
well = '2490010352304200541' OR
well = '2490010352304100541' OR
well = '2490010352313900541' OR
well = '2490010352304000541' OR
well = '2490010352290600541' OR
well = '2490010352307000541' OR
well = '2490010352325200541' OR
well = '2490010352306800541' OR
well = '2490010352306700541' OR
well = '2490010352266600541' OR
well = '2490010352266800541' OR
well = '2490010352266400541' OR
well = '2490010352311900541'
ORDER BY SORT_COL

I am selecting 20 unique wells from a table called XY. The selection of those wells works fine by itself. The problem occurs when I add the ORDER BY clause. I cannot "ORDER BY" well because the resulting sort will not give me what I need. I must have the wells in the order that I have specified. I am attempting to use CASE to establish that order but numerous different variations on my query always ends with the "missing operator" error message. What am I missing?

Thanks,

Paul Hudgens
Denver, CO"

carrey
Starting Member

22 Posts

Posted - 2004-05-18 : 08:57:30

Quick 'n dirty way would be to select INTO a temp table with the first query then select from there with your ORDER BY SORT_COL.

HTH

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-05-18 : 09:00:13

Did you try this?

SELECT well
FROM XY
WHERE
well = '2490010352320100541' OR
well = '2490010352317700541' OR
well = '2490010352314000541' OR
well = '2490010352319500541' OR
well = '2490010352317100541' OR
well = '2490010352203900541' OR
well = '2490010352318700541' OR
well = '2490010352304200541' OR
well = '2490010352304100541' OR
well = '2490010352313900541' OR
well = '2490010352304000541' OR
well = '2490010352290600541' OR
well = '2490010352307000541' OR
well = '2490010352325200541' OR
well = '2490010352306800541' OR
well = '2490010352306700541' OR
well = '2490010352266600541' OR
well = '2490010352266800541' OR
well = '2490010352266400541' OR
well = '2490010352311900541'
ORDER BY
CASE
WHEN well = '2490010352320100541' THEN 1
WHEN well = '2490010352317700541' THEN 2
WHEN well = '2490010352314000541' THEN 3
WHEN well = '2490010352319500541' THEN 4
WHEN well = '2490010352317100541' THEN 5
WHEN well = '2490010352203900541' THEN 6
WHEN well = '2490010352318700541' THEN 7
WHEN well = '2490010352304200541' THEN 8
WHEN well = '2490010352304100541' THEN 9
WHEN well = '2490010352313900541' THEN 10
WHEN well = '2490010352304000541' THEN 11
WHEN well = '2490010352290600541' THEN 12
WHEN well = '2490010352307000541' THEN 13
WHEN well = '2490010352325200541' THEN 14
WHEN well = '2490010352306800541' THEN 15
WHEN well = '2490010352306700541' THEN 16
WHEN well = '2490010352266600541' THEN 17
WHEN well = '2490010352266800541' THEN 18
WHEN well = '2490010352266400541' THEN 19
WHEN well = '2490010352311900541' THEN 20
END

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-05-18 : 09:06:39

[code]
create table #xy (well varchar(20))

insert into #xy
select '2490010352320100541' union select '2490010352317700541' union select '2490010352314000541'
union select '2490010352319500541' union select '2490010352317100541' union select '2490010352203900541'
union select '2490010352318700541' union select '2490010352304200541' union select '2490010352304100541'
union select '2490010352313900541' union select '2490010352304000541' union select '2490010352290600541'
union select '2490010352307000541' union select '2490010352325200541' union select '2490010352306800541'
union select '2490010352306700541' union select '2490010352266600541' union select '2490010352266800541'
union select '2490010352266400541' union select '2490010352311900541'

SELECT well,
CASE
WHEN well = '2490010352320100541' THEN 1
WHEN well = '2490010352317700541' THEN 2
WHEN well = '2490010352314000541' THEN 3
WHEN well = '2490010352319500541' THEN 4
WHEN well = '2490010352317100541' THEN 5
WHEN well = '2490010352203900541' THEN 6
WHEN well = '2490010352318700541' THEN 7
WHEN well = '2490010352304200541' THEN 8
WHEN well = '2490010352304100541' THEN 9
WHEN well = '2490010352313900541' THEN 10
WHEN well = '2490010352304000541' THEN 11
WHEN well = '2490010352290600541' THEN 12
WHEN well = '2490010352307000541' THEN 13
WHEN well = '2490010352325200541' THEN 14
WHEN well = '2490010352306800541' THEN 15
WHEN well = '2490010352306700541' THEN 16
WHEN well = '2490010352266600541' THEN 17
WHEN well = '2490010352266800541' THEN 18
WHEN well = '2490010352266400541' THEN 19
WHEN well = '2490010352311900541' THEN 20
END AS SORT_COL
FROM #xy
WHERE
well in ('2490010352320100541','2490010352317700541','2490010352314000541','2490010352319500541','2490010352317100541',
'2490010352203900541','2490010352318700541','2490010352304200541','2490010352304100541','2490010352313900541',
'2490010352304000541','2490010352290600541','2490010352307000541','2490010352325200541','2490010352306800541',
'2490010352306700541','2490010352266600541','2490010352266800541','2490010352266400541','2490010352311900541')
ORDER BY
CASE
WHEN well = '2490010352320100541' THEN 1
WHEN well = '2490010352317700541' THEN 2
WHEN well = '2490010352314000541' THEN 3
WHEN well = '2490010352319500541' THEN 4
WHEN well = '2490010352317100541' THEN 5
WHEN well = '2490010352203900541' THEN 6
WHEN well = '2490010352318700541' THEN 7
WHEN well = '2490010352304200541' THEN 8
WHEN well = '2490010352304100541' THEN 9
WHEN well = '2490010352313900541' THEN 10
WHEN well = '2490010352304000541' THEN 11
WHEN well = '2490010352290600541' THEN 12
WHEN well = '2490010352307000541' THEN 13
WHEN well = '2490010352325200541' THEN 14
WHEN well = '2490010352306800541' THEN 15
WHEN well = '2490010352306700541' THEN 16
WHEN well = '2490010352266600541' THEN 17
WHEN well = '2490010352266800541' THEN 18
WHEN well = '2490010352266400541' THEN 19
WHEN well = '2490010352311900541' THEN 20
END

drop table #xy
[/code]

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-05-19 : 04:06:07

As usual, a derived table would come in real handy here. Tweaking Rick's query:


SELECT * FROM 
(
	SELECT well,
	CASE 
	WHEN well = '2490010352320100541' THEN 1
	WHEN well = '2490010352317700541' THEN 2
	WHEN well = '2490010352314000541' THEN 3
	WHEN well = '2490010352319500541' THEN 4
	WHEN well = '2490010352317100541' THEN 5
	WHEN well = '2490010352203900541' THEN 6
	WHEN well = '2490010352318700541' THEN 7
	WHEN well = '2490010352304200541' THEN 8
	WHEN well = '2490010352304100541' THEN 9
	WHEN well = '2490010352313900541' THEN 10
	WHEN well = '2490010352304000541' THEN 11
	WHEN well = '2490010352290600541' THEN 12
	WHEN well = '2490010352307000541' THEN 13
	WHEN well = '2490010352325200541' THEN 14
	WHEN well = '2490010352306800541' THEN 15
	WHEN well = '2490010352306700541' THEN 16
	WHEN well = '2490010352266600541' THEN 17
	WHEN well = '2490010352266800541' THEN 18
	WHEN well = '2490010352266400541' THEN 19
	WHEN well = '2490010352311900541' THEN 20
	END AS SORT_COL
	FROM #xy
	WHERE 
	well in ('2490010352320100541','2490010352317700541','2490010352314000541','2490010352319500541','2490010352317100541',
	'2490010352203900541','2490010352318700541','2490010352304200541','2490010352304100541','2490010352313900541',
	'2490010352304000541','2490010352290600541','2490010352307000541','2490010352325200541','2490010352306800541',
	'2490010352306700541','2490010352266600541','2490010352266800541','2490010352266400541','2490010352311900541')
) A
ORDER BY SORT_COL

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-19 : 04:58:30

Put the values you are interested in into a table - it will save typing it all twice (or 3 times). Then you can join to that table.
If you don't want a permamnent table then use a table variable in the SP.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources