DB005156-7 concatenating columns in SQL

Document ID : KB000121349
Last Modified Date : 20/11/2018
Show Technical Document Details
Issue:
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