All Forums Tools
cadambank 2 posts Joined 01/15
12 Jan 2015
TPT : ERROR using BYTE data comparison in JOBSCRIPT

hi,
I am having problem with the following script.
The table I am using is

Create table Testdb.testtable

(

 COL1 CHAR(10),
 COL2 DECIMAL(9)

) PRIMARY INDEX(COL1);

and the following data
000179 AAAAAAAAAA     
            CCCCCCCCCCF44444   --------------> byte values "data1"
            1111111111  F00000
-----------------------
000180 BBBBBBBBBB
            CCCCCCCCCCF00000   --------------> byte values "data2"
            2222222222  F00001                
DEFINE JOB TESTJOB       
DESCRIPTION 'TESTTABLE'   
(                             
   DEFINE SCHEMA TEST_SCHEMA
   DESCRIPTION 'TEST_SCHEMA'           
 (
 COL1 CHAR(10),
 NULL01 BYTE,
 COL2 DECIMAL(9)
 );
   DEFINE OPERATOR UPDATE_OPERATOR
   DESCRIPTION 'UPDATE OPERATOR' 
   TYPE UPDATE                   
   SCHEMA *                      
   ATTRIBUTES    
 (
 VARCHAR TDPID=@TDPID,                          
 VARCHAR USERNAME=@USERNAME,                    
 VARCHAR USERPASSWORD=@USERPASSWORD,            
 VARCHAR PRIVATELOGNAME='<PVTLOGNAME>',
 INTEGER MAXSESSIONS=10,                        
 INTEGER MINSESSIONS=1,                         
 VARCHAR TARGETTABLE='TESTDB.TESTTABLE',  
 VARCHAR ERRORTABLE1='TESTDB.ET_TESTTABLE',
 VARCHAR ERRORTABLE2='TESTDB.UV_TESTTABLE',
 VARCHAR LOGTABLE='TESTDB.LG_TESTTABLE',  
 VARCHAR WORKTABLE ='TESTDB.WT_TESTTABLE'                 
 );
   DEFINE OPERATOR READ_OPERATOR 
   DESCRIPTION 'READ FILE'       
   TYPE DATACONNECTOR PRODUCER   
   SCHEMA ACC_OPN_SCHEMA         
   ATTRIBUTES                    
   (                             
       VARCHAR FILENAME='DD:DDIN',
       VARCHAR INDICATORMODE='N', 
       VARCHAR OPENMODE='READ',   
       VARCHAR FORMAT='UNFORMATTED'
   ) ;                        
   STEP LOAD_TABLES
   (              
     APPLY        
     (
    'INSERT INTO TESTDB.TESTTABLE
  (
  COL1
  ,COL2
  )
  VALUES
  (
  TRIM(COL1),
  :COL2
  );'
   ) TO OPERATOR(UPDATE_OPERATOR())
 SELECT
 COL1
 ,CASE WHEN NULL01 = 'FF'XB THEN NULL
  ELSE COL2 END AS COL2
 FROM OPERATOR(READ_OPERATOR());
   );
);
 
The error i am getting is
TPT_INFRA: Syntax error at or near line 80 of Job Script File 'dd:SYSIN':
TPT_INFRA: At "SELECT" missing SEMICOL_ in Rule: STEP                   
Compilation failed due to errors. Execution Plan was not generated.     
Job script compilation failed.                                          
This is because of the line
 
CASE WHEN NULL01 = 'FF'XB THEN NULL
ELSE COL2 END AS COL2
 
as byte comparison is failed to be recognised as such,i think :)
 
Also if i define the COL2 as CHAR in the layout , the Data2 is going to error tables and data1 is going to the target table.
as such by specifying the jobscript as "SELECT * FROM OPERATOR" the data1 gets inserted in to error table and data2 in to target table.
can you guys help me with this?

feinholz 1234 posts Joined 05/08
12 Jan 2015

The TPT syntax does not yet support "XB" format.

--SteveF

cadambank 2 posts Joined 01/15
12 Jan 2015

Ok....thanks feinholz.... Another way i can think of is load the data in a byte field and then convert the byte field in to decimal..... As its a packed decimal, is there a way to convert that?..... Direct byte conversion is an problem, any indirect ways??

You must sign in to leave a comment.