bteq blank Spaces

Tools & Utilities
Enthusiast

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

2 REPLIES
Enthusiast

Re: bteq blank Spaces

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

Junior Contributor

Re: bteq blank Spaces

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