TPT : ERROR using BYTE data comparison in JOBSCRIPT

Tools

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?

2 REPLIES
Teradata Employee

Re: TPT : ERROR using BYTE data comparison in JOBSCRIPT

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

-- SteveF

Re: TPT : ERROR using BYTE data comparison in JOBSCRIPT

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??