Provide a technique to concatenate a character and a numeric column in CA Datacom SQL.

Products

• Datacom
• DATACOM - AD
• Ideal
• CIS
• COMMON SERVICES FOR Z/OS
• 90S SERVICES
• DATABASE MANAGEMENT SOLUTIONS FOR DB2 FOR Z/OS
• COMMON PRODUCT SERVICES COMPONENT
• Common Services
• CA ECOMETER SERVER COMPONENT FOC
• Easytrieve Report Generator for Common Services
• INFOCAI MAINTENANCE
• IPC
• UNICENTER JCLCHECK COMMON COMPONENT
• Mainframe VM Product Manager
• CHORUS SOFTWARE MANAGER
• CA ON DEMAND PORTAL
• CA Service Desk Manager - Unified Self Service
• PAM CLIENT FOR LINUX ON MAINFRAME
• MAINFRAME CONNECTOR FOR LINUX ON MAINFRAME
• GRAPHICAL MANAGEMENT INTERFACE
• WEB ADMINISTRATOR FOR TOP SECRET
• Xpertware

Issue/Introduction

Description:

Given the following SQL statements to create a test Datacom Table with test data:

 DROP   TABLE TESTNUM;                                              
 CREATE TABLE TESTNUM (                                             
   CHR4 CHAR(4),                                                    
   NUM8 NUMERIC(8)    ) ;                                           
 INSERT INTO TESTNUM VALUES ('A234', 1);                            
 INSERT INTO TESTNUM VALUES ('B234', 1234567);                      
 INSERT INTO TESTNUM VALUES ('C234', 12345678);      

The concatenation symbol in an SQL statement is "||".

Each of these attempts to concatenate the character column and the numeric column will result in error:

 SELECT CHR4 || NUM8                FROM TESTNUM; 
 SELECT CHR4 || (DECIMAL(NUM8,8,0)) FROM TESTNUM;
                               
 SQLCODE = -41, SQLSTATE=42818                                     
 MESSAGE = DATATYPES OF OPERATION '??' NOT COMPATIBLE

Solution:

The SQLCODE -41 indicates that the data types involved are invalid for the type of expression.

DECIMAL numbers in SQL are defined as packed decimal in the CA Datacom Datadictionary while NUMERIC columns are defined as zoned decimal.

So, conversion of the NUMERIC column using SQL functions is needed to supply a valid data type for concatenation.

Use the SQL functions DECIMAL and DIGITS:

First, use the DECIMAL function to convert the NUMERIC value to a DECIMAL.
Then, use the DIGITS function to convert the converted decimal number to character.

 SELECT CHR4 || DIGITS(DECIMAL(NUM8,8,0))             FROM TESTNUM;                                         
                                                                       
Result...                                    
                                 
 A23400000001                                                          
 B23401234567                                                          
 C23412345678

Also, add the substring function SUBSTR to further manipulate the result.

SELECT CHR4 || SUBSTR(DIGITS(DECIMAL(NUM8,8,0)),1,4) FROM TESTNUM;     
 
Result... 
                                    
 A2340000                                                              
 B2340123                                                              
 C2341234  

Another example with SUBSTR:

 SELECT CHR4 || SUBSTR(DIGITS(DECIMAL(NUM8,8,0)),5,4) FROM TESTNUM;       
                                                                          
Result...                                         
 A2340001                                                                
 B2344567                                                                
 C2345678                                                                

Environment

Release: DATABB00200-14-Datacom/AD
Component: