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;
.SET SEPARATOR 0;
.SET NULL AS "";
.SET TITLEDASHES OFF;
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 (
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.
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.
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.
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.)
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.
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