TPT SelectStmt

Tools
Enthusiast

TPT SelectStmt

I am trying to EXPORT the following SelectStmt    SelectStmt = 'SELECT 'GRANT '||rolename||' TO USERDAMIN WITH ADMIN OPTION;' FROM dbc.roleinfo;',

this is failing because the inclusion of the single quote within the statement is not recognised by the syntax.  How can this be achieved?

Tags (1)
5 REPLIES
Enthusiast

Re: TPT SelectStmt

Use two single quotes as follows:

SelectStmt = 'SELECT ''GRANT'' ||rolename|| ''TO USERDAMIN WITH ADMIN OPTION;'' FROM dbc.roleinfo;'

N/A

Re: TPT SelectStmt

Has anyone tried multi line sqls in SELECTSTMT.? an working example would be grateful.

Teradata Employee

Re: TPT SelectStmt

We strip newlines from within the requests so it should not be a problem.

(I have seen SELECT requests in TPT scripts that span pages.)

-- SteveF
Teradata Employee

Re: TPT SelectStmt

Hi,

I'm using TPT 14.00.00.03, both on Windows and AIX. I am exporting data using $EXPORT template.

It seems that escaping single quotes by doubling them does not work for SelectStmt. I am not able to provide any string literal in SELECT.

I am specyfying in a script (just a sample, that anyone can exercise):

Set SelectStmt          = 'SELECT DatabaseName,TableName,TableKind from dbc.tables where databasename=''DBC'';';

and the TPT gives me an error message coming from RDBMS:

TPT_INFRA: TPT05014: RDBMS error 3706: Syntax error: expected something between a string or a Unicode character literal and the word 'DBC'.

When I look into DBQL, I see the same error message and the SQL that I see in QueryText column contains doubled single quotes:

SELECT DatabaseName,TableName,TableKind from dbc.tables where databasename=''DBC'';

From old forum posts it seems that it worked for previous versions. 

Jacek

Teradata Employee

Re: TPT SelectStmt

I have never seen the escaping fail.

Therefore, please upgrade to the latest version of 14.00 (efix #3 is a very old version of the 14.0 release) and see if that fixes your problem.

-- SteveF