SQL Function to translate a code into a description
Article ID: 10378
Updated On:
Products
Issue/Introduction
How can I simulate an IF() function call in CA-IDMS/SQL?
Some dialects of SQL include a scalar function IF(x, y, z) (sometimes called IFF()), where the function returns y if x is TRUE and z otherwise. CA-IDMS/SQL has no such function. Such a function can be useful if you want to translate a code into a more descriptive column value without having to create a related table.
For example, assume the following table and data:
CREATE TABLE SQLSCHM.DISNEYCHARACTER
( ID UNSIGNED NUMERIC(4) NOT NULL,
SURNAME CHARACTER(15) NOT NULL,
GIVEN_NAME CHARACTER(10) NOT NULL,
GENDER_CODE CHARACTER(1) NOT NULL
);
INSERT INTO SQLSCHM.DISNEYCHARACTER VALUES (1, 'MOUSE', 'MICKEY', 'M');
INSERT INTO SQLSCHM.DISNEYCHARACTER VALUES (2, 'MOUSE', 'MINNIE', 'F');
INSERT INTO SQLSCHM.DISNEYCHARACTER VALUES (3, 'DUCK', 'DONALD', 'D');
You may want to use a SELECT like this to return “MALE” or “FEMALE” instead of “M” or “F”:
SELECT ID, SURNAME, GIVEN_NAME,
IF(GENDER_CODE='M','MALE ',IF(GENDER_CODE='F','FEMALE','******')) AS GENDER
FROM SQLSCHM.DISNEYCHARACTER;
This won’t work because CA-IDMS has no IF() scalar function.
Environment
Resolution
The following technique can be used to simulate this behaviour:
SELECT ID, SURNAME, GIVEN_NAME,
SUBSTRING('******MALE FEMALE', 6*LOCATE(GENDER_CODE, 'MF')+1, 6) AS GENDER
FROM SQLSCHM.DISNEYCHARACTER;
*+
*+ ID SURNAME GIVEN_NAME GENDER
*+ -- ------- ---------- ------
*+ 1 MOUSE MICKEY MALE
*+ 2 MOUSE MINNIE FEMALE
*+ 3 DUCK DONALD ******
*+
*+ 3 rows processed
The LOCATE() call returns 1 if GENDER_CODE=”M”, 2 if GENDER_CODE=”F” and 0 otherwise. The arithmetic on the returned value creates an appropriate offset in the “******MALE FEMALE” string for the SUBSTRING() call to return the desired result.
Additional Information
Feedback
