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)
8 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

Junior Contributor

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.

Enthusiast

Re: BTEQ Data Truncation while Exporting

I am having a same problem. The only difference is my output is a SQL query of 10230 characters which is generated by executing a Macro. That macro gives correct result when directly run in the d/b. But the O/P is getting trimmed when written in a file.
I tried the with the Width=720 and also deleted the old file. But it's still not working for me.

Please  help.

 

Thanks

S Gupta
Junior Contributor

Re: BTEQ Data Truncation while Exporting

So WIDTH=720 is not large enough for your result, simply increase it.

Enthusiast

Re: BTEQ Data Truncation while Exporting

@dnoeth I have already tried with the

WIDTH=1000;
WIDTH=10000;

and also the Maximum

WIDTH=65531;

But nothing seems to be working.

S Gupta
Enthusiast

Re: BTEQ Data Truncation while Exporting

@dnoeth

The problem has been resolved.

Sorry for the inconvenience.
I just found out, there is a variable that was setting a different width, which was overiding the one that I set.

Thanks for the help though.

 

 

Thanks & Regards,

Sneha

S Gupta