All Forums Tools
Darych 22 posts Joined 03/14
27 Mar 2014
TPT load to table which contains special characters in name and column names

Hi to all,
I want to load data using TPT utilities into table which has special characters in table name and column names. In my code I build dml statement:
`INSERT INTO "aidar"."AID ! test"( "1ID", "2ID", "3ID", "DATA !11", "Ch <!-- eck") VALUES (:"AID !_0_1ID_00001", :"AID !_0_2ID_00002", :"AID !_0_3ID_00003", :"AID !_0_DATA !11_00004", :"AID !_0_Ch <!-- ec_00005");`
 
But I've got error message "Syntax error: Blank name in quotation marks".
Does anybode knows what I can do with this error?
Thanks.
P.S. I understand that I use strange names, but I'm implementing quotation support for product so I should test it =)

feinholz 1234 posts Joined 05/08
27 Mar 2014

I need to see more of the script.
The error message might be referring to some other part of the script.
 

--SteveF

Darych 22 posts Joined 03/14
28 Mar 2014

It's a full script. I'm using TPT API im my source code, and I'm sending only this statement to Teradata.
Could you say please can I use names with spcial characters in TPT? If yes, where I can read about this special case? What is the difference between common load and load into tables with special characters?
Thank you very much.

feinholz 1234 posts Joined 05/08
28 Mar 2014

Yes, you can have special characters in database object names, as long as you enclose them in double-quotes, which you have.
You will have to obtain the syntax information from the Teradata SQL manuals (maybe one of those characters in the quotes is not allowed, I do not know all of the rules by heart).
TPTAPI does not parse SQL.
Whatever your application gives to us, we send to Teradata.
I have to assume, then, that the error you are getting is from Teradata.
 

--SteveF

Darych 22 posts Joined 03/14
31 Mar 2014

Thank you very much. I'll try to go deeper in what happens investigating.

Darych 22 posts Joined 03/14
04 Apr 2014

I've solved issue. I have incorrect placed quotes in error table names. Thank you very much. Now all is working good.

serbaobat 1 post Joined 04/14
06 Apr 2014

thank you information

zammohan 6 posts Joined 04/14
29 Apr 2014

Hello Feinholz, 
I am trying to use a where clause inside TPT export selector. I am getting syntax error for the following definition due to the quotes present in IN LIST of where clause. I cannot avoid the quotes for the list as its a varchar filed. Is there any way out to solve this, may be using some escape character?
Thanks In Advance.
/*** Export Operator Definition ***/
 
   DEFINE OPERATOR EXPORT_OPERATOR
    DESCRIPTION 'TERADATA PARALLEL TRANSPORTER EXPORT OPERATOR'
    TYPE EXPORT
    SCHEMA PLT_IWH_BASE
    ATTRIBUTES
    (
      VARCHAR PrivateLogName = 'exportoper_privatelog',
       INTEGER MaxSessions    =  8,
       INTEGER MinSessions,
       VARCHAR TdpId          = 'XXXXX',
       VARCHAR UserName       = 'XXXXXX',
       VARCHAR UserPassword   = 'XXXXXX',
       VARCHAR SelectStmt     = 'SELECT BATCH_PK,
MATERIAL_PK,
SITE_PK,
BATCH_ID,
BATCH_DESCRIPTION,
MANUFACTURE_DATE_ACTUAL,
EXPIRATION_DATE_ACTUAL,
VERIFICATION_FLG,
CREATED_DATE,
UPDATE_DATE,
AUDIT_ID,
LANGUAGE_ID,
SOURCE_SYSTEM_ID,
GLOBAL_BATCH_ID,
BATCH_INSPECTION_DT,
RELEASE_DT,
BATCH_SEQUENCE_DATE,
STG_OUT_PK,
BATCH_STATUS from PLT_IWH_BASE.BATCH where BATCH_ID IN ('000362464WH', '20550K01A');'
 

Best,
Shyam

feinholz 1234 posts Joined 05/08
29 Apr 2014

Just double the single quotes.

--SteveF

py 7 posts Joined 03/16
06 Apr 2016
VARCHAR SelectStmt = 'SELECT CAST(((CASE T1.R WHEN 1 THEN  ''NombreTicketsDeb='' WHEN 2 THEN  ''NombreTicketsFin='' END) 
  || ''' /* Here I want a single quote. In SQL assistant I use 3 single quote 
but I don't know how to do in TPT because when I put 5 there is an error 
of missing quote and when I put four the quote doesn't appear in the result */
  || TRIM(CAST(CASE T1.R WHEN 1 THEN T1.L WHEN 2 THEN T1.L + '||@NombreTickets||' END AS  CHAR(6))) 
|| ''' /* Here the same */
) AS CHAR(25))
   AS LOG_AUDIT FROM 
  (SELECT TOP 2 '||@NombreTickets||' AS L, ROW_NUMBER() OVER (ORDER BY L ) AS R FROM CASINO_USEREXIT_TICKETLEC) AS T1;'

Hi,
I have a similar question, how do I escape the single quote character in the SelectStatement in TPT ?
I've tried to write with three to five single quote or inside a double quote but nothing works.
Any idea please ????
thanks,
py
 

dnoeth 4628 posts Joined 11/04
06 Apr 2016

Should be the same, to get a single quote you need to double it within the string, thus your string should be four quotes:
#1 starts the string, #2 & 3 is the escaped quote and #4 closes the string...

Dieter

You must sign in to leave a comment.