Skip to Content

Unload with data conversion with CASE and INTO

This example uses a CASE statement to unload a character, based on a date field, and shows the INTO clause which can also convert datatypes. Datatypes can also be converted using scalar functions in the select rather than the INTO clause (next example).

Information
Example
//UNLOAD   EXEC PGM=NGTUTIL,PARM='ssid,,NORESTART'          
//*                                                         
//UTPRINT   DD SYSOUT=*                                     
//SYSPRINT  DD SYSOUT=*                                     
//SYSTSPRT  DD SYSOUT=*                                     
//OBJREPRT  DD SYSOUT=*                                     
//*                                                         
//SYSIN     DD *                                            
 OUTPUT SYSREC DSNAME hlq.OUTDATA.&TS..P&PART..T&TIME   
 UNLOAD                                                    
   DIRECT YES
      SELECT col15,                                        
             CASE WHEN (col26 IS NULL OR                   
                        col26 > CURRENT TIMESTAMP) THEN 'A'
                 WHEN (col26 IS NOT NULL AND              
                        col26 < CURRENT TIMESTAMP) THEN 'I'
                  ELSE 'X' END,                            
             col03                                         
        INTO colA DECIMAL(4,2),                            
             colB CHAR(1),                                 
             colC CHAR(2)                                  
        FROM creator.table                            
       WHERE A.col01 < 11;                                 
/*

Related topics

Examples-of-BMC-AMI-Unload-jobs

BMC-AMI-Unload-command-and-syntax-reference


 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*

BMC AMI Unload for Db2 13.1