Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Number mapping and IF in SQL

Posted on 1999-06-25
12
Medium Priority
?
222 Views
Last Modified: 2010-03-19
Hi, I know very little about SQL, and I have the following 2 similar questions:
1). How do I map a number to a string in a query ? That is, I have a field called [STATUS] in my table. I want to create a SQL statement ("SELECT blah as blah from MYTABLE blah") which will put "One" in the Status column if [STATUS]=1, "Two" if [STATUS]=2, etc.
2). I have two other fields, up to two of which can be empty (that is, ""). I want to create a SQL statement that will put [FIELD1] in the Result column if it is nonempty; otherwise, it will put [FIELD2] in the Result column.
Thanks...
0
Comment
Question by:Sinclair
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 2

Author Comment

by:Sinclair
ID: 1095951
Edited text of question.
0
 
LVL 1

Expert Comment

by:simoraikov
ID: 1095952
1)
select blah=(case status
  when 1 then 'One'
  when 2 then 'Two'
...................
  else  'Somethig Else'
end)
from mytable

2)
select field1, field2,
blah=(case field1
         when '' field2
         else field1
end
)
from mytable
0
 
LVL 2

Author Comment

by:Sinclair
ID: 1095953
This is what I have:

SELECT Level=(case [STATUS]
when 0 then 'Zero'
when 1 then 'One'
else 'Other' end)
from MYTABLE

And Micro$oft Access gives me a syntax error there, always.
What do I do now ?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 5

Expert Comment

by:mayhew
ID: 1095954
1) Often mapping like that is accomplished by storing your relationships in another table that you include in your join.

So you might create a table called MapTable (for example) that looks like:

Name    Newval
====    ======
'ONE'       1
'TWO'       2


Then your query would look like

Select a.Field1, a.Field2, b.Newval
From MyTable a, MapTable b
Where a.Status = b.Name

2) For a choice like you describe, you'll want to use immediate if (IIF).  It looks like

Select IIF(Field1 is null, Field2, Field1)
From MyTable


Let me know if this is helpful.
0
 
LVL 2

Author Comment

by:Sinclair
ID: 1095955
Thanks Mayhew, IIF works perfectly, and I was already thinking of using the lookup table like you said.I just rejected the answer because I have a follow-up question, as it were: can I use anything other than a lookup table ? Nested IIFs, perhaps (I haven't tried that yet..) ? I have many other numeric fields that need to be expressed as strings, and making a bunch of lookup tables seems kludgy. Also, is it possible to have a DBGRID control that will run such a SQL query, and map the numbers to images (i.e. icons) ? Thanks...
0
 
LVL 9

Expert Comment

by:david_levine
ID: 1095956
Sinclair,

Just to throw a comment in...

You *want* to use lookup tables. That's the advantage of  using a database. Let the computer do the work, instead of you. Databases are written to do table joins efficiently.

Lets say someone asks for a new report, as an example. Each place you need to do something with the data, you'll be writing your enormous IIF's. Or if someone else wants to access the data, they won't know what you did. Or, if a new value is created, you need to change every single IIF in every report / application, instead of just adding one row to a lookup table.

Use reference tables, and even views which could do it behind the scenes. That way, any development language can be used accessing the same SQL to return the same results.

As you mention in your last comment, lets say, instead of displaying a value of "One", you wanted to display an image of the number "1". You could add a column to your lookup table that's a character string which contains a url or filename to use as the image and then load that file after reading the SQL.

David
0
 
LVL 5

Expert Comment

by:mayhew
ID: 1095957
Yes, you can use nested IIF's if you prefer.

But, as david excellently pointed out, making your database do the work is more efficient and easier to maintain.

As another thought, if you have a lot of awkward mapping to do, maybe you could consider just tracking the values you actually want in your main table.

And yes, you can map to whatever you want.  From string to integer was just an example based on your original question.
0
 
LVL 2

Author Comment

by:Sinclair
ID: 1095958
Thanks, David, I understand that lookup tables are much more flexible. It's just that I will have so MANY of them if I totally switch to using them everywhere... In any case, with the images, what object would read that filename so that the DBGRID control could display it automatically ?
0
 

Expert Comment

by:lajpat
ID: 1095959
Instead of Select-Case you can try for switch statement

SELECT switch([Status]=1, "One", [Status]=2, "Two" ........)
FROM MyTable;
0
 
LVL 9

Expert Comment

by:david_levine
ID: 1095960
Sinclair,

Yes, you will have MANY lookup tables. That's the way things work. Is your solution more appropraite? That would depend. If it wasn't going to get bigger or have multiple people accessing it or have multiple applications accessing it, then it doesn't really matter. But if any of those situations might happen, I'd suggest using the MANY lookup tables. Otherwise, everyone will need to hard code (read bad) the lookup tables in their apps.

I'm not familiar with the DBGrid control you are talking about so I can't help you there.

David
0
 
LVL 2

Author Comment

by:Sinclair
ID: 1095961
Ok, I got it... David, could you please submit an answer so that I can grade it and give you the points ? Thanks :-)
0
 
LVL 9

Accepted Solution

by:
david_levine earned 240 total points
ID: 1095962
Good luck!

David
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question