All Forums Tools
23 Dec 2013
bteq blank Spaces

I am very new to Teradata. I am using bteq to unload data from data base. I have to unload data for multiple file so i using generic unix script and passing each sql as a file to breq (.run file <sql_file>). I am getting the data with column names and spaces in the file. I want to remove them, kindly advise ?
 
Output file format:
cntry_grp_type_cd|cntry_grp_cd|cntry_cd
R                |EMEA        |TD
L                |ISC         |MV
IR               |APAC        |AU
IR               |EMEA        |FR
L                |BEE         |TM
R                |LAC         |GP
R                |EMEA        |BT
L                |SGP         |MM
IR               |EMEA        |ES
 
I am looking like as follows:
R|EMEA|TD
L|ISC|MV
IR|APAC|AU
IR|EMEA|FR
L|BEE|TM
R|LAC|GP
R|EMEA|BT
L|SGP|MM
IR|EMEA|ES

Raja_KT 1246 posts Joined 07/09
25 Dec 2013

Hi,

Did you try with trim and cast together? I hope you will be using  .SET SIDETITLES OFF; .SET TITLEDASHES OFF; .SET RECORDMODE OFF; 

example:

 

select CAST( field1 || '|' ||

 

field2 || '|' ||

 

(CASE WHEN field3 IS NULL

 

THEN '?'

 

ELSE field3

 

END) || '|' ||

 

 

 

.......

 

 

 

(CASE WHEN dt1 IS NULL

 

THEN '?'

 

ELSE CAST(CAST(dt1 AS FORMAT 'YYYY-MM-DD') AS CHAR(10))

 

END)

 

AS CHAR( ) (TITLE '')

 

from table1

 

where

 

field = xxx

 

 

 

or each and everyfield :

 

 

 

select

 

trim(cast(field1 as char(5))) (title ''),

 

cast('|' as char(1)).... from table1....

 

 

Cheers,

 

Raja

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
27 Dec 2013

You can also switch to TPT, the latest releases added a VARTEXT output format without any CAST/TRIM.

Dieter

You must sign in to leave a comment.