BTEQ Trimming only last Select Column

Teradata Applications
Highlighted
Enthusiast

BTEQ Trimming only last Select Column

Hi,

 

New to Teradata, having some issues with BTEQ export. Help would be much appreciated.

The query below select 3 columns which I would like to trim blank spaces, for some odd reason, only account_md(last col) would trim.

 

See below for Query & result.

 

.LOGON xxxx/yyyyy,zzzz

.SET SEPARATOR '|';
.SET TITLEDASHES OFF;
.SET RECORDMODE ON;
.SET WIDTH 20000;
.set session tworespbufs on;
.set session respbuflen max64;
.EXPORT REPORT FILE = "C:\Users\test.txt"

SELECT
TRIM(TRAILING FROM account_status) AS account_status,
TRIM(account_cm) AS account_cm,
TRIM(account_md) AS account_md

FROM
Database
sample 5;
.Export reset;
.logoff;

.EXIT;

Capture.PNG

 


Accepted Solutions
Junior Contributor

Re: BTEQ Trimming only last Select Column

The TABLE is actually part of the syntax, not the source table name.

I did a cut&paste, but forgot to include the WITH part:

WITH t AS
 (
   SELECT * FROM amsa_dept_db.apac_srg_master_iata_list
 )
SELECT *
FROM TABLE(CSV(NEW VARIANT_TYPE(
Trim(t.account_status) AS account_status,
Trim(t.account_cm) AS account_cm,
Trim(t.account_md) AS account_md)
,'|',
'')
RETURNS (op VARCHAR(25) CHARACTER SET Latin)) AS t1;

 

1 ACCEPTED SOLUTION
9 REPLIES
Senior Apprentice

Re: BTEQ Trimming only last Select Column

Hi,

 

I think your basic problem is the column definitions and your 'export report' command.

 

'Export report' tells BTEQ to write the data out as a 'report' - formatted data values, columns in each row lined up under the previous one etc. Thus each value will be 'expanded' to the maximum width based on the column definition as returned by the dbms.

 

As an example, if column 'account_status' is defined as CHAR(255), even if the value for that column in a single row is 'abc' then the column definition returned to BTEQ will be VARCHAR(255) - where 255 is the maximum number of characters. BTEQ will then put each value into a report line, allowing 255 characters to hold it.

 

I think what you want is 'export data'. This will give you an output file which contains the data value without 'extra' padding. Bear in mind that you will also get unprintable characters which are either record lengths (2 bytes at start of each output record) and before each variable length field.

 

If you just want the 'data', you might want to look at the 'csv' functions, which may not be available to you depending on which version of TD you are using.

 

HTH

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: BTEQ Trimming only last Select Column

Hi Dave,

 

Thank you for replying, I have Version 15.10. 

 

Can you please provide a link with more information on the 'csv' function? Searching keywords csv function and BTEQ is not exactly bringing me anything helpful.

 

Thanks again.

Senior Apprentice

Re: BTEQ Trimming only last Select Column

No problem.

 

Try the following link TD 15.0 CSV function

 

HTH

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Junior Contributor

Re: BTEQ Trimming only last Select Column

You find details about CSV in the SQL Functions and Operators manual.

In your case it should be

SELECT * 
FROM TABLE(CSV(NEW VARIANT_TYPE(
  Trim(Trailing From account_status) AS account_status,
  Trim(account_cm) AS account_cm,
  Trim(account_md) AS account_md
  ,'|',
  '')
RETURNS (op VARCHAR(64000) CHARACTER SET Latin)) AS t1;

CSV automatically CASTs all columns to VarChar, thus TRIM is only needed for CHARs.

Enthusiast

Re: BTEQ Trimming only last Select Column

Hi dnoeth,

 

Thank you so much for the write up, but I'm encountering the below syntax error. It is possible that I don't have the csv function installed?

 

             *** Failure 3707 Syntax error, expected something like ';' between the word 'apac_srg_master_iata_list' and '('.
             Statement# 2, Info =43

 

Here is my BTEQ,

 

.LOGON xxxx/yyyyy,zzzzz

.SET SEPARATOR '|';
.SET TITLEDASHES OFF;
.SET RECORDMODE ON;
.SET WIDTH 20000;
.set session tworespbufs on;
.set session respbuflen max64;
.EXPORT REPORT FILE = "C:\test.csv";

SELECT *
FROM amsa_dept_db.apac_srg_master_iata_list(CSV(NEW VARIANT_TYPE(
Trim(account_status) AS account_status,
Trim(account_cm) AS account_cm,
Trim(account_md) AS account_md)
,'|',
'')
RETURNS (op VARCHAR(25) CHARACTER SET Latin)) AS t1;
.Export reset;
.logoff;

.EXIT;

 

Junior Contributor

Re: BTEQ Trimming only last Select Column

The TABLE is actually part of the syntax, not the source table name.

I did a cut&paste, but forgot to include the WITH part:

WITH t AS
 (
   SELECT * FROM amsa_dept_db.apac_srg_master_iata_list
 )
SELECT *
FROM TABLE(CSV(NEW VARIANT_TYPE(
Trim(t.account_status) AS account_status,
Trim(t.account_cm) AS account_cm,
Trim(t.account_md) AS account_md)
,'|',
'')
RETURNS (op VARCHAR(25) CHARACTER SET Latin)) AS t1;

 

Enthusiast

Re: BTEQ Trimming only last Select Column

It works!! Thank you so dnoeth!

Enthusiast

Re: BTEQ Trimming only last Select Column

Hi doneth,

 

What does this 'op' do? Can't find any explanation online.

With the export, I end up with the extra row 'op', not a big deal. Just wondering if I can remove it automatically.

     

       RETURNS (op VARCHAR(1000) CHARACTER SET Latin)) AS t1;

 

 

Teradata Employee

Re: BTEQ Trimming only last Select Column

This is the name of the CSV generated column.

Instead of 

SELECT *

you can use 

SELECT OP (title '')