IDMS

  • 1.  Tech Tip: How can I simulate an IF() function call in CA-IDMS/SQL?

    Broadcom Employee
    Posted Jun 27, 2017 03:00 PM

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

     

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

    Instructions:

    The following technique can be used to simulate this behaviour:

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



  • 2.  Re: Tech Tip: How can I simulate an IF() function call in CA-IDMS/SQL?

    Posted Jun 27, 2017 05:31 PM

    *** is not a valid column name.  I think the political correctness module changed the original name.  lol...

     

    John



  • 3.  Re: Tech Tip: How can I simulate an IF() function call in CA-IDMS/SQL?

    Broadcom Employee
    Posted Jun 28, 2017 01:26 AM

    Indeed. I wrote that document and I did think it might have been a problem. But it seems to be OK if you view the actual Knowledge Document here: SQL Function to translate a code into a description.

    I may re-write it so that it is a little more innocent.