BTEQ Data Truncation while Exporting

Tools
Enthusiast

BTEQ Data Truncation while Exporting

Hello,

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;

sELECT

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

.logoff;

Thanks,

stat

Tags (1)
4 REPLIES
Enthusiast

Re: BTEQ Data Truncation while Exporting

Stat,

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

Rglass

Enthusiast

Re: BTEQ Data Truncation while Exporting

Rglass,

As per your suggestion I have just tried with .set width 720; 

But no luck. The result is still the same(truncated).

Thanks,

stat

Senior Apprentice

Re: BTEQ Data Truncation while Exporting

WIDTH 720 should not truncate.

Double check the result, did you forget to delete the output file and BTEQ appended the result?

Enthusiast

Re: BTEQ Data Truncation while Exporting

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.