SQL Function to translate a code into a description

Document ID:  TEC1049355
Last Modified Date:  06/16/2017
{{active ? 'Hide' : 'Show'}} Technical Document Details

Products

  • CA IDMS

Releases

  • CA IDMS:Release:18.5
  • CA IDMS:Release:19.0

Components

  • CA IDMS/DB:IDMS
  • CA IDMS SQL:SQLOPT
Introduction:

How can I simulate an IF() function call in CA-IDMS/SQL?

Background:

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,
    SEX                         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(SEX='M','MALE  ',IF(SEX='F','FEMALE','******')) AS GENDER
  FROM SQLSCHM.DISNEYCHARACTER;

 

This won’t work because CA-IDMS has no IF() scalar function.

Environment:
CA-IDMS all supported releases.
Instructions:

The following technique can be used to simulate this behaviour:

SELECT ID, SURNAME, GIVEN_NAME,
  SUBSTRING('******MALE  FEMALE', 6*LOCATE(SEX, '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 SEX=”M”, 2 if SEX=”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:

For more information, see the following CA IDMS DocOps pages:

SQL Reference

CA IDMS Scalar Functions

Please help us improve!

Will this information enable you to resolve your issue?

Please tell us what we can do better.

{{feedbackText.length ? feedbackText.length : '0'}}/255

{{status}}

Not what you were looking for?

Search Again >

Product Information

Support by Product >

Communities

Join a Community >

Chat with CA

Just give us some brief information and we'll connect you to the right CA ExpertCA sales representative.

Our hours of availability are 8AM - 5PM CST.

All Fields Required

connecting

We're matching your request.

Unfortunately, we can't connect you to an agent. If you are not automatically redirected please click here.

  • {{message.agentProfile.name}} will be helping you today.

    View Profile


  • Transfered to {{message.agentProfile.name}}

    {{message.agentProfile.name}} joined the conversation

    {{message.agentProfile.name}} left the conversation

  • Your chat with {{$storage.chatSession.messages[$index - 1].agentProfile.name}} has ended.
    Thank you for your interest in CA.


    Rate Your Chat Experience.

    {{chat.statusMsg}}

agent is typing