TPT LOAD APPLY STATEMENT DDL OPERATOR ISSUE

Tools
Enthusiast

TPT LOAD APPLY STATEMENT DDL OPERATOR ISSUE

I am having an issue with the Apply statement below in Bold and Italics.

I am using the DDL Apply statement to empty my table before I load it.

But I am getting some syntax issue that I can't figure out.

If I remove the DDL Apply statement everything works fine and the data is imported to the table but then I have to empty the table in another script outside of TPT and would rather not do that.

Thanks

 

DEFINE JOB IMPORT_DATA_FROM_A_FILE_TO_A_TABLE
DESCRIPTION 'IMPORT TEXT FILE TO A TABLE'
(

DEFINE SCHEMA prf_SCHEMA
(
BUSINESS_FMT VARCHAR(8),
SALES_TYPE VARCHAR(5),
BUSINESS_FMT_NBR VARCHAR(5),
GRP_DIV VARCHAR(4),
AMOUNT_1 VARCHAR(100),
AMOUNT_2 VARCHAR(100),
AMOUNT_3 VARCHAR(100),
AMOUNT_4 VARCHAR(100),
AMOUNT_5 VARCHAR(100),
AMOUNT_6 VARCHAR(100),
AMOUNT_7 VARCHAR(100),
AMOUNT_8 VARCHAR(100),
AMOUNT_9 VARCHAR(100),
AMOUNT_10 VARCHAR(100)
);

DEFINE OPERATOR DDL_OPERATOR
TYPE DDL
ATTRIBUTES
(
VARCHAR PRIVATELOGNAME = 'DATACONNECTOR_PRIVATELOG',
VARCHAR TDPID = '',
VARCHAR USERNAME = '',
VARCHAR USERPASSWORD = '$tdwallet(password_C)'
);


DEFINE OPERATOR IMPORT_OPERATOR
TYPE DATACONNECTOR PRODUCER
SCHEMA prfmrgn_t_SCHEMA
ATTRIBUTES
(
VARCHAR DirectoryPath= '/data/',
VARCHAR FileName = '''' || @strTableName || '.txt''',
VARCHAR Format = 'Delimited',
VARCHAR OpenMode = 'Read',
VARCHAR TextDelimiter =','
);

DEFINE OPERATOR LOAD_OPERATOR
TYPE LOAD
SCHEMA prf_SCHEMA
ATTRIBUTES
(
VARCHAR PrivateLogName = 'loadoper_privatelog',
VARCHAR TargetTable = 'prf.' || @strTableName,
VARCHAR TDPID = '',
VARCHAR USERNAME = '',
VARCHAR USERPASSWORD = '$tdwallet(password_C)',
VARCHAR ErrorTable1 = 'prf.LOADOPER_ERRTABLE1',
VARCHAR ErrorTable2 = 'prf.LOADOPER_ERRTABLE2',
VARCHAR LogTable = 'prf.LOADOPER_LOGTABLE'
);

APPLY
('DELETE FROM prf.' || @strTableName || ';''')
TO OPERATOR (DDL_OPERATOR);

 

APPLY $INSERT
TO OPERATOR (LOAD_OPERATOR [1])
SELECT * FROM OPERATOR (IMPORT_OPERATOR [2]);


);


Accepted Solutions
Teradata Employee

Re: TPT LOAD APPLY STATEMENT DDL OPERATOR ISSUE

Ignore my prior post - I was thinking of something else. You need to define multiple steps.

 

STEP CLEANUP (
APPLY
('DELETE FROM prf.' || @strTableName || ';')
TO OPERATOR (DDL_OPERATOR);
);
 
STEP RELOAD (
APPLY $INSERT
TO OPERATOR (LOAD_OPERATOR [1])
SELECT * FROM OPERATOR (IMPORT_OPERATOR [2]);
);











1 ACCEPTED SOLUTION
8 REPLIES
Junior Contributor

Re: TPT LOAD APPLY STATEMENT DDL OPERATOR ISSUE

IMHO the final two quotes must be removed to get a valid SQL string:

 

('DELETE FROM prf.' || @strTableName || ';')

 

Enthusiast

Re: TPT LOAD APPLY STATEMENT DDL OPERATOR ISSUE

Thank you for the quick reply Dieter and I cant believe I missed the quotes... :(

I made the correction but I am still getting this error below...

TPT_INFRA: Syntax error at or near line 447 of Job Script File '/data/sql/LOAD.tpt':
TPT_INFRA: At "APPLY" missing RPAREN_ in Rule: DEFINE JOB
Compilation failed due to errors. Execution Plan was not generated.

 

Everything looks correct in the script so I am puzzled...

Thank You

Junior Contributor

Re: TPT LOAD APPLY STATEMENT DDL OPERATOR ISSUE

Hard to tell, is line 447 after that APPLY?

APPLY
('DELETE FROM prf.' || @strTableName || ';')
TO OPERATOR (DDL_OPERATOR);

No right parenthesis missing here ...

Enthusiast

Re: TPT LOAD APPLY STATEMENT DDL OPERATOR ISSUE

Line 447 is 

APPLY $INSERT
TO OPERATOR (LOAD_OPERATOR [1])
SELECT * FROM OPERATOR (IMPORT_OPERATOR [2]);

 

The funny thing is that it is NOT the first APPLY statement but the second one that works fine WHEN the first APPLY statement is removed.

Crazy...

Thanks

Teradata Employee

Re: TPT LOAD APPLY STATEMENT DDL OPERATOR ISSUE

This post was incorrect and has been removed.

 

 

Enthusiast

Re: TPT LOAD APPLY STATEMENT DDL OPERATOR ISSUE

thanks Fred but could you give me an example of what I should do. I don't understand what you mean by your answer. thank you!
Teradata Employee

Re: TPT LOAD APPLY STATEMENT DDL OPERATOR ISSUE

Ignore my prior post - I was thinking of something else. You need to define multiple steps.

 

STEP CLEANUP (
APPLY
('DELETE FROM prf.' || @strTableName || ';')
TO OPERATOR (DDL_OPERATOR);
);
 
STEP RELOAD (
APPLY $INSERT
TO OPERATOR (LOAD_OPERATOR [1])
SELECT * FROM OPERATOR (IMPORT_OPERATOR [2]);
);











Enthusiast

Re: TPT LOAD APPLY STATEMENT DDL OPERATOR ISSUE

Thanks Fred! Works perfectly.

I had no idea I had to have the "STEP CLEANUP" AND "STEP RELOAD" statement before the open paren.

 

Thanks again