All Forums Tools
sarvesh_kumar 6 posts Joined 03/11
22 Aug 2012
Exporting data using a BTEQ

Hi,

I am trying ot export data to a flat file using a BTEQ.

I am getting an errneous situation in setting the target file format. First I am listing the code:-

.EXPORT DATA FILE = $TGT_FILE_DIR/$OUTPUT_FILENAME

.SET WIDTH 65531;
.SET RECORDMODE OFF;
.RETLIMIT 2147483407,2048;
.SET SEPARATOR 0;
.SET NULL AS "";
.SET TITLEDASHES OFF;
.SHOW CONTROLS;

select
CAST(SEQUENCE_NUMBER AS CHAR(11)),
CAST(VIEW_NAME AS CHAR(30)),
CAST(UNIX_IP AS CHAR(30)),
CAST(VENDOR_NAME AS CHAR(15)),
CAST(SUBJECT_AREA AS CHAR(15)),
CAST(SOURCE_SYSTEM AS CHAR(15)),
CAST(TIME_OF_INSERT AS CHAR(26))FROM (
Sel
SEQUENCE_NUMBER,
VIEW_NAME,
UNIX_IP,
VENDOR_NAME,
SUBJECT_AREA,
SOURCE_SYSTEM,
TIME_OF_INSERT
from extract_control_table) FINAL_TABLE;

i have cast the field SEQUENCE_NUMBER as CHAR(11), hence i expect that the field will occupy 11 charcters in the flat file. The 2nd field data will start at the position 12 in the flat file. But I see that the data of the 2nd field is starting from 16th position. On analysis I find that the field name SEQUENCE_NUMBER is of 15 charcters and hence, the the cast operation is overwritten.

I want to overcome this constraint and want to set the starting position and the length of each field as casted to in the query.

Thanks

Sarvesh.  
 

CarlosAL 512 posts Joined 04/08
22 Aug 2012

You should RTFM:

.EXPORT DATA FILE

DATA         Returns the results of a query to a file for processing by other programs. BTEQ returns data in Record Mode and stores it in FastLoad format output file. Use this format to interchange data between BTEQ, FastLoad, and FastExport. MultiLoad also accepts the FastLoad format.

HTH

Cheers.

Carlos.

 

sarvesh_kumar 6 posts Joined 03/11
24 Aug 2012

Thanks Carlos... but is there any way of achieving it.

I have a requirement to achieve the same using a BTEQ and not a fast export script.

sivaji.gv2 2 posts Joined 08/12
24 Aug 2012

Try exporinting in 'REPORT' mode and use your sql to format the data the way you want.

Many developers use this method and export all data as pipe delimited using fexp. (i.e. concatenate all columns with pipe and export it as a one single big char field.)
 

eg:

SEL

CAST(SEQUENCE_NUMBER AS CHAR(11))  ||
CAST(VIEW_NAME AS CHAR(30))               ||
CAST(UNIX_IP AS CHAR(30))                     ||
...

 

If you are exporting hundres of thousands of rows use FEXP for slightly better performance.

Sivaji GV
Teradata DBA

ulrich 816 posts Joined 09/09
24 Aug 2012

and don't forget the (title'')

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

30 Aug 2015

That's correct. If we won't place (TITLE '') at the end of columns, header would be added to the file and would look awkward. We could skip the header while importing data by skipping first 2 lines as below.
.import vartext ',' file=/root/file.txt,skip=2

You must sign in to leave a comment.