TPT with UDF.

Tools
Enthusiast

TPT with UDF.

ERROR:

----------------

TPT_INFRA: TPT02092: Error: Invalid single-byte character: (

TPT_INFRA: TPT02932: Error: Invalid token near line 138 (text was '"(CASE WHEN VALUE_TYPE = ''R'' THEN SYSLIB.PTY_FLOATENC (CAST (VAL AS FLOAT),''TDM'',32,1,4) ELSE NULL END)"')

TPT_INFRA: TPT02092: Error: Invalid single-byte character: (

TPT_INFRA: TPT02932: Error: Invalid token near line 139 (text was '"(CASE WHEN VALUE_TYPE = ''C'' THEN SYSLIB.PTY_VARCHARLATINENC(VAL,''TDM'',112,1,5) ELSE NULL END)"')

TPT_INFRA: Semantic error at or near line 119 of Job Script File '/export/home1/edwwher/tmp//GEADW_TDM_ETL1100024.ctl':

TPT_INFRA: TPT03107: Operator 'STREAM_OPERATOR' has explicit input schema. Restricted APPLY

  statement allows only deferred schema for consumer operators.

TPT_INFRA: Semantic error at or near line 119 of Job Script File '/export/home1/edwwher/tmp//GEADW_TDM_ETL1100024.ctl':

TPT_INFRA: TPT03111: Rule: Restricted APPLY Statement

TPT_INFRA: TPT02092: Error: Invalid single-byte character: (

TPT_INFRA: TPT02932: Error: Invalid token near line 140 (text was '"(CASE WHEN VALUE_TYPE = ''I'' THEN SYSLIB.PTY_INTEGERENC(CAST (VAL AS INTEGER),''TDM'',16,1,8) ELSE NULL END)"')

TPT_INFRA: TPT02092: Error: Invalid single-byte character: (

TPT_INFRA: TPT02932: Error: Invalid token near line 141 (text was '"(CASE WHEN VALUE_TYPE = ''H'' THEN SYSLIB.PTY_VARCHARLATINENC(VAL,''TDM'',16,1,6) ELSE NULL END)"')

TPT_INFRA: TPT02092: Error: Invalid single-byte character: (

TPT_INFRA: TPT02932: Error: Invalid token near line 142 (text was '"(CASE WHEN VALUE_TYPE = ''E'' THEN SYSLIB.PTY_VARCHARLATINENC(VAL,''TDM'',16,1,7) ELSE NULL END)"')

Compilation failed due to errors. Execution Plan was not generated.

Job script compilation failed.

Hello,

 

We have been trying to use a FUNCTION defiend to encrypt the data while loading to be used in TPT. Earlier we have done the same thing using MLOAD and it has been running fine. But when we call the FUNCTION in TPT it is giving us errors: 

CODE:

--------

USING CHARACTER SET ASCII

DEFINE JOB ld_TDM_PARAM_LIVE_DEV_SSTABLE_FROM_FILE

DESCRIPTION 'LOAD ld_TDM_PARAM_LIVE_DEV_SS TABLE FROM A FILE'

(

  DEFINE SCHEMA ld_TDM_PRM_LV_DV_SS_SCHEMA

  DESCRIPTION 'TABLE ld_TDM_PARAM_LIVE_DEV_SS SCHEMA'

  (

      "FILE_INDX"                                VARCHAR(50)

    , "SCAN_NO"                                  VARCHAR(50)

    , "PARAM"                                    VARCHAR(259)

    , "VAL"                                      VARCHAR(503)

    , "VALUE_TYPE"                               VARCHAR(13)

    , "UNIT"                                     VARCHAR(503)

    , "TEST_PARAM_RESULT_VALUE"                  VARCHAR(32)

    , "TEST_PARAM_RESULT_ASCII"                  VARCHAR(112)

    , "TEST_PARAM_RESULT_INTEGER"                VARCHAR(16)

    , "TEST_PARAM_RESULT_HEX"                    VARCHAR(32)

    , "TEST_PARAM_RESULT_EXCEPN_HEX"             VARCHAR(32)

  );

  DEFINE OPERATOR DDL_OPERATOR()

  DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DDL OPERATOR'

  TYPE DDL

  ATTRIBUTES

  (

    VARCHAR PrivateLogName = 'ld_TDM_PRM_LV_DV_SS_DDL',

    VARCHAR TdpId = @tdpid,

    VARCHAR UserName = @UserName,

    VARCHAR UserPassword = @UserPassword,

    VARCHAR AccountID,

    VARCHAR ErrorList = '3807'

  );

  DEFINE OPERATOR STREAM_OPERATOR()

  DESCRIPTION 'TERADATA PARALLEL TRANSPORTER STREAM OPERATOR'

  TYPE STREAM 

  SCHEMA ld_TDM_PRM_LV_DV_SS_SCHEMA

  ATTRIBUTES

  (

    INTEGER MaxSessions = 4,

    INTEGER MinSessions = 1,

    VARCHAR TargetTable = 'GEADW_TDM_S.ld_TDM_PARAM_LIVE_DEV_SS',

    VARCHAR TdpId = @tdpid,

    VARCHAR UserName = @UserName,

    VARCHAR UserPassword = @UserPassword,

    VARCHAR AccountId,

    VARCHAR ErrorTable1 = 'GEADW_TDM_S.ld_TDM_PRM_LV_DV_SS_E1',

    VARCHAR ErrorTable2 = 'GEADW_TDM_S.ld_TDM_PRM_LV_DV_SS_E2',

    VARCHAR WorkTable = 'GEADW_TDM_S.ld_TDM_PRM_LV_DV_SS_WT',

    VARCHAR LogTable = 'GEADW_TDM_S.ld_TDM_PRM_LV_DV_SS_LT'

  );

  DEFINE OPERATOR FILE_READER()

  DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DATA CONNECTOR OPERATOR'

  TYPE DATACONNECTOR PRODUCER

  SCHEMA ld_TDM_PRM_LV_DV_SS_SCHEMA

  ATTRIBUTES

  (

    VARCHAR PrivateLogName='DCLog',

    VARCHAR ArchiveDirectoryPath = @ArchiveDirectoryPath,

    VARCHAR OpenMode = @OpenMode,

    VARCHAR DirectoryPath = @DirectoryPath,

    VARCHAR IndicatorMode = @IndicatorMode,

    INTEGER VigilWaitTime = @VigilWaitTime,

    INTEGER VigilElapsedTime = @VigilElapsedTime,

    VARCHAR Format = @Format,

    VARCHAR TextDelimiter = @TextDelimiter,

    VARCHAR FileName = '*params.txt',

    VARCHAR acceptexcesscolumns=@acceptexcesscolumns,

    VARCHAR acceptmissingcolumns=@acceptmissingcolumns,

    VARCHAR TruncateColumns=@TruncateColumns,

    VARCHAR RowErrFileName=@RowErrFileName

  );

  STEP setup_tables

  (

    APPLY

    ('DROP TABLE GEADW_TDM_S.ld_TDM_PRM_LV_DV_SS_E1;'),

    ('DROP TABLE GEADW_TDM_S.ld_TDM_PRM_LV_DV_SS_E2;'),

    ('DROP TABLE GEADW_TDM_S.ld_TDM_PRM_LV_DV_SS_WT;'),

    ('DROP TABLE GEADW_TDM_S.ld_TDM_PRM_LV_DV_SS_LT;')

    TO OPERATOR (DDL_OPERATOR () );

  );

  STEP load_data_to_table

  (

    APPLY

    (

      'INSERT INTO GEADW_TDM_S.ld_TDM_PARAM_LIVE_DEV_SS

      (

          :FILE_INDX

        , :SCAN_NO

        , :PARAM

        , :TEST_PARAM_RESULT_VALUE

        , :TEST_PARAM_RESULT_ASCII

        , :TEST_PARAM_RESULT_INTEGER

        , :TEST_PARAM_RESULT_HEX

        , :TEST_PARAM_RESULT_EXCEPN_HEX

        , :VALUE_TYPE

        , :UNIT

      );'

    )

    TO OPERATOR ( STREAM_OPERATOR () [1] )

    SELECT 

            "FILE_INDX"

          , "SCAN_NO"

          , "PARAM"

          , "(CASE WHEN VALUE_TYPE = ''R'' THEN SYSLIB.PTY_FLOATENC (CAST (VAL AS FLOAT),''TDM'',32,1,4) ELSE NULL END)"

          , "(CASE WHEN VALUE_TYPE = ''C'' THEN SYSLIB.PTY_VARCHARLATINENC(VAL,''TDM'',112,1,5) ELSE NULL END)"

          , "(CASE WHEN VALUE_TYPE = ''I'' THEN SYSLIB.PTY_INTEGERENC(CAST (VAL AS INTEGER),''TDM'',16,1,8) ELSE NULL END)"

          , "(CASE WHEN VALUE_TYPE = ''H'' THEN SYSLIB.PTY_VARCHARLATINENC(VAL,''TDM'',16,1,6) ELSE NULL END)"

          , "(CASE WHEN VALUE_TYPE = ''E'' THEN SYSLIB.PTY_VARCHARLATINENC(VAL,''TDM'',16,1,7) ELSE NULL END)"

          , "VALUE_TYPE"

          , "UNIT"

    FROM OPERATOR (FILE_READER() [1] );

  );

);

Sorry for the format but I was unable to paste it propeprly here.

1 REPLY
Teradata Employee

Re: TPT with UDF.

The SELECT clause of the APPLY-SELECT statement is supposed to contain column names.

There are several issues with the script:

1. You enclosed the CASE logic in double-quotes, which is being interpreted as the name of the column (and you have embedded quotes within quotes, which may not be working either).

2. You are trying to direct these function calls to a file reader, which will not work.

It is not clear what you are trying to attempt.

If you would provide the corresponding MultiLoad script, I can take a look and see if we can figure out how to get this to work for you.

-- SteveF