All Forums Tools
stat 5 posts Joined 09/14
12 Sep 2014
BTEQ Data Truncation while Exporting



Seems like the following is a common issue that I have been facing  Truncation while exporting using BTEQ.I serached in google but could not find a good answer thus posting here. I am creating a '|' delemited file using BTEQ and encapsulating all the selected fields  with " " in the output in UNIX environment. But not all the fields are getting exported to the output and getting truncate after column 75. PLease help.


Here is the below script that I am using.


.run file /u/users/analytics/.logons.txt;

.EXPORT  report file = /u/analytics/data/item.dat;

.set recordmode off;

.set width 100000;


top 10

'"' || trim(coalesce(cast(item_nbr  AS VARCHAR(20)),'?'))||'"|"'||

trim(coalesce(cast(Old_nbr  AS VARCHAR(20)),'?'))||'"|"'||

trim(coalesce(cast(Status  AS VARCHAR(5)),'?'))||'"|"'||

trim(coalesce(cast(upc  AS VARCHAR(30)),'?'))||'"|"'||

trim(coalesce(cast(cat_nbr  AS VARCHAR(10)),'?'))||'"|"'||

trim(coalesce(cast(sub_cat_nbr  AS VARCHAR(10)),'?'))||'"|"'||

trim(coalesce(cast(description  AS VARCHAR (80)),'?'))||'"|"'||

trim(coalesce(cast(brand  AS VARCHAR (80)),'?'))||'"|"'||

trim(coalesce(cast(pb_flag  AS VARCHAR(20)),'?'))||'"|"'||

trim(coalesce(cast(manufacturer_name  AS VARCHAR (80)),'?'))||'"|"'||

trim(coalesce(cast(distributor_name  AS VARCHAR (80)),'?'))||'"|"'||

trim(coalesce(cast(pack_nbr  AS VARCHAR(30)),'?'))||'"|"'||

trim(coalesce(cast(size_nbr  AS VARCHAR(30)),'?'))||'"|"'||

trim(coalesce(cast(size_desc  AS VARCHAR (6)),'?'))||'"|"'||

trim(coalesce(cast(likeitemgroup   AS VARCHAR (100)),'?'))||'"|"'||

trim(coalesce(cast(linecode  AS VARCHAR(20)),'?'))||'"|"'||

trim(coalesce(cast(Corelist  AS VARCHAR(20)),'?'))||'"|"'||

trim(coalesce(cast(PricingReportExceptionItem  AS VARCHAR(20)),'?'))||'"|"'||

trim(coalesce(cast(Automarkup  AS VARCHAR(20)),'?'))||'"|"'||

trim(coalesce(cast(PricingSKU  AS VARCHAR(15)),'?')) || '"'


from wm_ad_hoc.dim_itemattributes;

.EXPORT reset





Glass 225 posts Joined 04/10
12 Sep 2014

the width shoud be giving an error. the max is 65531, Have you tried 720 ? 

stat 5 posts Joined 09/14
12 Sep 2014

As per your suggestion I have just tried with .set width 720; 
But no luck. The result is still the same(truncated).

dnoeth 4628 posts Joined 11/04
12 Sep 2014

WIDTH 720 should not truncate.
Double check the result, did you forget to delete the output file and BTEQ appended the result?


stat 5 posts Joined 09/14
12 Sep 2014

I am sorry. As Dieter said I missed to delete the old file when tried with width 720 option..Now the output is of full length and as expected.
Thank you both. Appreciate your help.

You must sign in to leave a comment.