15.10 - Example: Retrieve a PERIOD(TIME) Datatype - Preprocessor2 for Embedded SQL

Teradata Preprocessor2 for Embedded SQL Programmer Guide

prodname
Preprocessor2 for Embedded SQL
vrm_release
15.10
category
Programming Reference
featnum
B035-2446-035K

This example uses the casting operator to retrieve a PERIOD(TIME) Datatype.

    01  RETURNFIELD          PIC X(40) VALUE SPACES. 
    01  RETURNIND            PIC S9(2) COMP.         
                                                  
EXEC SQL                                 
	SELECT PERIODT (CHARACTER(40))           
        FROM TAB01                       
        INTO :RETURNFIELD :RETURNIND           
        WHERE ID = 44                    
END-EXEC.                                
                                         
01 CONVERTER.                                              
   02 HALF-VALUE.                                          
      05 BYTE1 PIC X.                                      
      05 BYTE2 PIC X.                                      
   02 BINARY-VALUE REDEFINES HALF-VALUE PIC S9(2) COMP-5.  

 

 * TIMESTAMP AND TIMESTAMP WITH TIMEZONE                      
  01  TIMESTAMPFULL.                                          
      02  TIMESTAMPX        PIC X(24).                        
      02  TIMESTAMPY REDEFINES TIMESTAMPX  OCCURS 2 TIMES.    
          05 TSSECONDS       PIC S9(9) COMP.                  
          05 TSYEAR          PIC S9(4) COMP.                  
          05 TSMONTH         PIC X.                           
          05 TSDAY           PIC X.                           
          05 TSHOUR          PIC X.                           
          05 TSMINUTE        PIC X.                           
          05 TSTZHOUR        PIC X.                           
          05 TSTZMINUTE      PIC X.                           
                                                              
      01  WORKSECS               PIC S9(2)V9(6). 
      01  DISPSECS               PIC Z9.9(6).    
 
 
	EXEC SQL                                       
		DECLARE CURA CURSOR FOR                   
		SELECT  PERIODTS FROM TAB01                        	
	END-EXEC.                                      
 
                                              
	EXEC SQL OPEN CURA                             
	END-EXEC.                                      
                                               
                                               
	PERFORM FETCH UNTIL SQLCODE NOT = 0.           
             
 
FETCH.                                          
	EXEC SQL FETCH CURA INTO                    
		:TIMESTAMP01 :TIMESTAMPIND 
	END-EXEC.                                   
    	IF SQLCODE = 0 THEN                         
              	IF TIMESTAMPIND = -1 THEN               
			DISPLAY 'TIMESTAMP IS NULL'          
		ELSE                                    
			MOVE TIMESTAMP01 TO TIMESTAMPX       
			PERFORM DISPLAY-TIMESTAMP            
		END-IF                                  
                                                
	ELSE                                        
		DISPLAY SQLCODE                         
	END-IF.                                     
                                  
 
  DISPLAY-TIMESTAMP.                                  
                                                      
      MOVE ZERO TO BINARY-VALUE.                      
                                                      
      DISPLAY ' '.                                    
                                                      
      MOVE TSMONTH(1) TO BYTE2.                       
      DISPLAY 'MONTH ' BINARY-VALUE.                  
                                                      
      MOVE TSDAY(1) TO BYTE2.                         
      DISPLAY 'DAY ' BINARY-VALUE.                    
                                                      
      DISPLAY 'YEAR ' TSYEAR(1).                      
                                                      
      MOVE TSHOUR(1) TO BYTE2.                        
      DISPLAY 'HOUR ' BINARY-VALUE.                   
                                                      
      MOVE TSMINUTE(1) TO BYTE2.                      
      DISPLAY 'MINUTE ' BINARY-VALUE.                 
                                                      
 * 15.16 SECONDS IS STORED AS 15160000                
      COMPUTE WORKSECS = TSSECONDS(1) / 1000000.      
      MOVE WORKSECS TO DISPSECS.                      
      DISPLAY 'SECONDS '  DISPSECS.                   
                                                      
      MOVE TSTZHOUR(1) TO BYTE2.                      
      DISPLAY 'TZ HOUR ' BINARY-VALUE.                
                                                      
      MOVE TSTZMINUTE(1) TO BYTE2.                    
      DISPLAY 'TZ MINUTE ' BINARY-VALUE.              
                                                      

The prior program is for big endian systems (for example, on mainframes). For a little endian system, reverse BYTE1 and BYTE2 in 01 Converter:

01 CONVERTER.
	 02 HALF-VALUE.
			 05 BYTE2 PIC X.
			 05 BYTE1 PIC X.
	 02 BINARY-VALUE REDEFINES HALF-VALUE PIC S9(2) COMP-5.