All Forums Tools
terasum 13 posts Joined 10/12
18 Feb 2014
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,
 

terasum 13 posts Joined 10/12
18 Feb 2014

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.

M.Saeed Khurram 544 posts Joined 09/12
18 Feb 2014

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

Adeel Chaudhry 773 posts Joined 04/08
18 Feb 2014

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

-- If you are stuck at something .... consider it an opportunity to think anew.

mceoni 2 posts Joined 03/12
23 Apr 2014

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 !

You must sign in to leave a comment.