create volatile table sample_table AS (sel * from appl_dwmdm_01.v_ied_inv_sfp_lot_inv_dtl sample 1000) with data ON COMMIT PRESERVE ROWS; create volatile table supPlan_inv_Result AS (sel * from appl_dwmdm_01.v_ied_inv_sfp_lot_inv_dtl sample 0) with data ON COMMIT PRESERVE ROWS;
INSERT INTO supPlan_inv_Result sel * from sample_table where sfp_id not in (select itm_dsc from appl_dwmdm_01.r_itm);
.export DATA FILE=test.txt sel * from supPlan_inv_Result; .LOGOFF .exit
the output file contain data which is not delimited properly and contain various unknowns letter like |- and many which are not even present in keyboard.
i have to import this data to a table. please do suggest the reason and possible solution
I had a similar problem trying to export the results of a query to a flat file on UNIX. I had to use .EXPORT REPORT instead of .EXPORT DATA. Then filter out the header information using the "awk" command to keep only the detail records.
.EXPORT DATA FILE=//filepath/outputfile.rpt SELECT F1 || '~' || F2 AS OUTPUT_RECORD FROM TABLE1 ; .EXPORT RESET;
The output file at this point will include the column header and a header seperator line. OUTPUT_RECORD --------------------- 001~FINANCE 002~MARKETING 003~MANUFACTURING
In your calling script filter out only the detail lines and put in another file. awk '/~/' //filepath/outputfile.rpt > //filepath/outputfile.txt
now you should have only your exported detail records in //filepath/outputfile.txt 001~FINANCE 002~MARKETING 003~MANUFACTURING