SQL technique to simulate IF() or CASE in IDMS

Products

• IDMS
• IDMS - Database

Issue/Introduction

This article describes how to simulate an IF() function call in the IDMS SQL option.

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. The IDMS SQL option has no such function. Such a function can be useful in order 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');

It may be necessary 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 IDMS has no IF() scalar function.

Environment

Release: All supported releases.

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.