Number mapping and IF in SQL

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.
Who is Participating?
david_levineConnect With a Mentor Commented:
Good luck!

SinclairAuthor Commented:
Edited text of question.
select blah=(case status
  when 1 then 'One'
  when 2 then 'Two'
  else  'Somethig Else'
from mytable

select field1, field2,
blah=(case field1
         when '' field2
         else field1
from mytable
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

SinclairAuthor Commented:
This is what I have:

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

And Micro$oft Access gives me a syntax error there, always.
What do I do now ?
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.
SinclairAuthor Commented:
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...

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.

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.
SinclairAuthor Commented:
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 ?
Instead of Select-Case you can try for switch statement

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

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.

SinclairAuthor Commented:
Ok, I got it... David, could you please submit an answer so that I can grade it and give you the points ? Thanks :-)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.