Exporting data using a BTEQ

[sarvesh_kumar]

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]

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]

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]

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.

[ulrich]

and don't forget the (title'')

[krishnatadepall]

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