Is there any "decode" function in Microsoft SQL syntax? 2

[rostik]

Hi everybody!
In Informix or Oracle there is a "decode" function. What it does is converts some available values in the field and changes to whatewer you ask. For examle:
SELECT employee_name, DECODE(employee_code, "A", "1", "B", "2", "C", "3&quot
FROM emp_table
(It will change "A" value to 1, "B" to 2 and so on.)
Is there any "decode" function available in Micorosoft SQL synax?

Thanks in advance,
Rostik

 

[CosmoKramer]

Check Access help for the Iif function....

 

[rostik]

Thanks, but Iif function works only if just 1 value should be changed, then for rest of the case data will be the same. I need to have ability to decode multiple values!
Any suggestions?

 

[BoxHead]

Rostik,

Look at the SWITCH FUNCTION. From Help, A97:
The following example uses the Switch function to determine the appropriate language for a specified city based on the values of the ShipCountry and ShipCity fields in an Orders table. You can enter the following expression in a calculated control on a form or report. The expression is shown on multiple lines for clarity; you can also enter it on a single line.

= Switch([ShipCity] = "Madrid", "Spanish", _
[ShipCity] = "Berlin", "German", _
[ShipCity] = "Torino", "Italian", _
[ShipCountry] = "France", "French", _
True, "English&quot

If the city is Madrid, the Switch function returns "Spanish"; if it is Berlin, it returns "German"; and so on. If the city is not one of those listed, but the country is France, it returns "French". If the city in question is not in the list, the Switch function returns "English".

I don't have time to build a replica of your db to test this, but the following should work if you are actually trying to replace letters of the alphabet with their character position in the alphabet.

Dim strOldVal as String
strOldVal = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
SELECT employee_name, Instr([employee_code],[strOldVal]) AS [Employee Code]FROM emp_table

To do the reverse (replace numbers with their Alpha equivalent), use the CHOOSE Function.


HTH

John

 

[THoey]

Seems like I am following Boxhead today. Look at the following in HELP:

IF ... THEN

ELSE

END IF

These can be nested as deep as you like. There is also a CASE statement:

Select Case testexpression
[Case expressionlist-n
[statements-n]] ...
[Case Else
[elsestatements]]
End Select

And as mentioned the SWITCH statement:

Switch(expr-1, value-1[, expr-2, value-2 … [, expr-n,value-n]])

One of these should work for you. Terry M. Hoey

 

[rostik]

Thanks very much.
Rostik
s-)