SELECT statement in a TPT EXPORT script

Tools & Utilities
Enthusiast

SELECT statement in a TPT EXPORT script

Hi,

I am trying to export data from a sql uery to a file using TPT Export operator.

My Sql query is running fine in sql assistant but when it is run through TPT it is giving non-existent syntax errors.

I have used two single quotes wherever I have one single quote in my query.

My requirements:

1) Want a TAB delimited file

2) Union of two sql queries is to be given as input

Query Snippet:

############################################

VARCHAR SelectStmt     = '

seleSELECT    

c1,c2,

CASE WHEN c3 IS NOT NULL AND c4<>0 THEN c4

     ELSE c5

END  AS AGE

from tablea

join tableb on a.c1=b.c1

union

sel c1,c2,c4 from tableg

where c2=''abc'' ;'

############################################

Error: something is expected between c5END and AS

Thanks,

Tags (2)
4 REPLIES
Enthusiast

Re: SELECT statement in a TPT EXPORT script

Can someone throw light on this.

My question was, I have a sql query which is working fine in SQL assistant but when used in TPT EXPORT in selectstmt='', it is giving me non-existent syntax errors.

Cant we use sql query as it is in TPT EXPORT, do we have to make any modifications before putting it into TPT ??

Thanks.

Enthusiast

Re: SELECT statement in a TPT EXPORT script

Hi,

You can enter a tab to the left of each line, or you can re-write it as follows:

VARCHAR SelectStmt     = '

seleSELECT    

c1,c2,

CASE WHEN c3 IS NOT NULL AND c4<>0 THEN c4

     ELSE c5 END  AS AGE

from tablea

join tableb on a.c1=b.c1

union

sel c1,c2,c4 from tableg

where c2=''abc'' ;'

Khurram
Teradata Employee

Re: SELECT statement in a TPT EXPORT script

'seleSELECT' .... ? Hope its not the actual code.

Fan

Re: SELECT statement in a TPT EXPORT script

I´m facing something like that too! When I try to get data from a Stage table, transform it on SelectStmt (using EXPORT Operator) I got an error message:

Teradata Parallel Transporter Export Operator Version 14.10.00.01

FROM_STG: private log specified: exp_wrk.log

Teradata Parallel Transporter Update Operator Version 14.10.00.01

TO_WRK: private log specified: upd_wrk.log

TO_WRK: connecting sessions

FROM_STG: TPT10551: CLI '215' occurred while connecting to the RDBMS

FROM_STG: TPT10507: CLI Error 215: MTDP: EM_CONNECT(215): Error found in local machine during connect.

FROM_STG: TPT10507: CLI Error 215: CLI error 215 (no message text available)

FROM_STG: Total processor time used = '0 Second(s)'

FROM_STG: Start : Tue Apr 22 14:32:20 2014

FROM_STG: End : Tue Apr 22 14:32:44 2014

TO_WRK: TPT10507: CLI Error 220: MTDP: EM_DBC_CRASH_A(220): Network connection to the DBC was lost.

(...)

My SelectStmt:

VARCHAR SelectStmt = '
LOCKING TABLE FINANCIAL.SAMPLE_STG FOR ACCESS
LOCKING TABLE FINANCIAL.SAMPLE_TGT FOR ACCESS
SELECT
T1.ID 
,T1.ATR1  
,T1.ATR2  
,T1.BUSS_DT 
,CURRENT_TIMESTAMP AS ST_DT 
,''9999-12-31'' AS END_DT 
,CURRENT_TIMESTAMP AS LD_DT
,T1.VRSION_CD 
,T1.RTRNB_IND 
,T1.MAP   
,CASE WHEN T2.ID IS NULL
        THEN ''I''
        ELSE CASE WHEN T1.BUSS_DT > T2.BUSS_DT AND
                       HASHROW(T1.ATR1, T1.ATR2, T1.VRSION_CD) <>
                       HASHROW(T2.ATR1, T2.ATR2, T2.VRSION_CD)
                  THEN ''A''
                  ELSE ''N''
                  END
        END UPD_TYPE_CD
  FROM FINANCIAL.SAMPLE_STG T1
 LEFT JOIN FINANCIAL.SAMPLE_TGT T2
   ON T1.ID = T2.ID
  AND T1.VRSION_CD = T2.VRSION_CD
  AND T2.END_DT = ''9999-12-31''
WHERE UPD_TYPE_CD <> ''N'';
');

I saw on a post that I wouldn´t be able to select current_timestamp on TPT. Is it true ?

Any suggestions would be very welcome!

Tks !