DB005156-7 concatenating columns in SQL

Products

• IDMS
• IDMS - Database
• IDMS - ADS

Issue/Introduction

This document describes the reason for a DB005156 or DB005157 message when concatenating columns in SQL.

Environment

CA IDMS, all supported releases.

Cause

The abend happens because the columns being concatenated must be either CHARACTER or BINARY and are not.

For example:

SELECT EMP_ID || ',' || BIRTH_DATE FROM DEMOEMPL.EMPLOYEE WHERE EMP_ID<2000;         
*+ Status = -4       SQLSTATE = 42604        Messages follow:                 
*+ DB005000 T86 C0M324: Message for module IDMSOCF, SQL statement number : 1. 
*+ DB005156 T86 C-4M324: Incompatible data types DTIME (internal) and UNSIGNED
*+ NUMERIC in expression

EMP_ID is UNSIGNED NUMERIC(4) and BIRTH_DATE is DATE.
The message could be DB005156 or DB005157 depending on the involved types.

Resolution

Use the CAST function to convert the data type of the involved columns to CHARACTER.

SELECT                                   
  CAST(EMP_ID AS CHAR(4)) || ',' ||      
  CAST(BIRTH_DATE AS CHAR(10))           
FROM DEMOEMPL.EMPLOYEE WHERE EMP_ID<2000;
*+                                       
*+ (EXPR)                                
*+ ------                                
*+ 1765,1955-11-13                       
*+ 1034,1951-11-23                       
*+ 1234,1969-10-19                       
*+ 1003,1951-08-02                       
*+                                       
*+ 4 rows processed

Additional Information

Expansion of Value-expression