NULL constant in IDMS SQL

Products

• IDMS

Issue/Introduction

How is it possible to use a literal constant NULL in the IDMS SQL Option ?

Environment

Release: All supported releases.
Component: IDMS SQL Option.

Cause

The IDMS SQL Option does not support a literal constant value of NULL.

SELECT DEPT_ID_0410, DEPT_NAME_0410 FROM EMPNET.DEPARTMENT
WHERE DEPT_ID_0410 < 5000
UNION
SELECT DEPT_ID_0410, NULL FROM EMPNET.DEPARTMENT
WHERE DEPT_ID_0410 >= 5000;
*+ Status = -4       SQLSTATE = 42501        Messages follow:
*+ DB005000 T182 C0M324: Message for module IDMSOCF, SQL statement number : 1.
*+ DB005155 T182 C-4M324: Column NULL not found

Resolution

When a literal constant value of NULL is to be used in IDMS SQL, the type must also be specified. Therefore, when coding a NULL value, use the CAST() function to specify the type.

SELECT DEPT_ID_0410, DEPT_NAME_0410 FROM EMPNET.DEPARTMENT
WHERE DEPT_ID_0410 < 5000
UNION
SELECT DEPT_ID_0410, CAST(NULL AS CHAR(45)) FROM EMPNET.DEPARTMENT
WHERE DEPT_ID_0410 >= 5000;
*+
*+ DEPT_ID_0410  DEPT_NAME_0410
*+ ------------  --------------
*+          100  EXECUTIVE ADMINISTRATION
*+         1000  PERSONNEL
*+         2000  ACCOUNTING AND PAYROLL
*+         3100  INTERNAL SOFTWARE
*+         3200  COMPUTER OPERATIONS
*+         4000  PUBLIC RELATIONS
*+         5100  <null>
*+         5200  <null>
*+         5300  <null>
*+
*+ 9 rows processed