Exporting data using a BTEQ

Tools
Enthusiast

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.  

Tags (2)
5 REPLIES
Junior Supporter

Re: Exporting data using a BTEQ

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.

Enthusiast

Re: Exporting data using a BTEQ

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.

Re: Exporting data using a BTEQ

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.

Supporter

Re: Exporting data using a BTEQ

and don't forget the (title'')

Re: Exporting data using a BTEQ

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