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

book

Article ID: 18249

calendar_today

Updated On:

Products

CA Datacom - DB CA Datacom CA Datacom - AD CA Datacom - Server CA Ideal CA CIS CA Common Services for z/OS CA 90s Services CA Database Management Solutions for DB2 for z/OS CA Common Product Services Component CA Common Services CA Datacom/AD CA ecoMeter Server Component FOC CA Easytrieve Report Generator for Common Services CA Infocai Maintenance CA IPC Unicenter CA-JCLCheck Common Component CA Mainframe VM Product Manager CA Chorus Software Manager CA On Demand Portal CA Service Desk Manager - Unified Self Service CA PAM Client for Linux for zSeries CA Mainframe Connector for Linux on System z CA Graphical Management Interface CA Web Administrator for Top Secret CA CA- 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: