Newbie query question - 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

SQL Server Development (2000)

Newbie query question

Author

Topic

sbt1
Yak Posting Veteran

89 Posts

Posted - 2004-01-21 : 15:23:39

I have a table with an integer field to represent a priority- 0 is 'High', 1 is 'Med', and 2 is 'Low'.

How do I write a query so that it will return "High", "Med", or "Low" text strings depending on the numerical value of the data in the field?

thx.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-21 : 15:29:30

Take a look at the CASE statement in Books Online. Something like this though:


SELECT 'Priority' = 
 CASE 
    WHEN priority = 0 THEN 'High'
    WHEN priority = 1 THEN 'Med'
    WHEN priority = 2 THEN 'Low'
  END
FROM Table1

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-01-21 : 17:09:50

I recommend creating a table of the priority values, and storing the text representations of those values in that table.

Priority, PriorityDesc (PK: Priority)
1,'High'
2,'Med'
3,'Low'

Then, to return the text, you JOIN to this table on the priority columns and include the PriorityDesc column in your query.

You will find this method easier to troubleshoot and maintain, since you won't need to repeat the CASE statement repeatedly in different places, and you can quickly change the descriptive values in 1 place.

select yourdata.*, PriorityDesc
from yourdata
inner join Priorities
on yourdata.priority = priorities.priority

(the word "priority" looks really weird when you type it a lot !!)

- Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-21 : 18:11:18

I second that recommendation.

I gotta stop just answering the question. It's better to provide a recommendation if a better one exists.

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-01-21 : 22:11:36

quote:

I gotta stop just answering the question. It's better to provide a recommendation if a better one exists.

I disagree -- nothing wrong with giving more than 1 side to every story ! and sometimes recommendations are great, but people just really need (or want) a quick and simple answer !

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources