Question Re: Exported data without LONG VARCHAR/LONG VARBINARY enabled in Technology Q&A

Exported data without LONG VARCHAR/LONG VARBINARY enabled

Former Member — Thu, 09 Oct 2014 00:59:33 GMT

Hi Guys,

I have one export query has been modified and now it exports in LONG VARCHAR format. Previously it did not and it just fit to the data table length. How can I adjust it back to fit as the data is?

The message I get during the export is

"Exported data includes LONG VARCHAR, LONG VARBINARY, and/or JAVA OBJECT columns. A width of 32768 bytes/characters will be assumed for these columns."

The below is the script:

SELECT micros.dly_srv_prd_trk_ttl.business_date,'000',
micros.dly_srv_prd_trk_ttl.store_id,
micros.dly_srv_prd_trk_ttl.rvc_seq,
micros.dly_srv_prd_trk_ttl.srv_period_seq,
'Cover Count' AS "Account",
replace((substring(micros.dly_srv_prd_trk_ttl.trk_cnt_62,1,9)),'         ','')
FROM micros.dly_srv_prd_trk_ttl

where cast((getDate()-1) As Date) = cast(micros.dly_srv_prd_trk_ttl.business_date As Date)

ORDER BY rvc_seq;

OUTPUT TO d:\\micros\\HBF\\Stat.csv FORMAT FIXED


However if I go to DBISQL and output the specific function:
    replace((substring(micros.dly_srv_prd_trk_ttl.trk_cnt_62,1,9)),'         ','')
It is exporting in a nice way where the data size is fit to the number instead of 32768 characters.
Please help.
Regards,
Christian

Re: Exported data without LONG VARCHAR/LONG VARBINARY enabled

VolkerBarth — Thu, 09 Oct 2014 03:14:11 GMT

You can use the builtin exprtype function to find out what data type is generated for a particular column in a result set. Starting with v10, you can also use the sa_describe_query system procedure to find out about the data type (and much more) of each column of a result set.

Note: You will have to mask any single quote within your query as these functions/procedures require the complete statement as a string, so double all single quotes...

After you have find out what expression returns an undesired data type, you can certainly cast that to a fitting data type, such as

..., CAST(replace((substring(micros.dly_srv_prd_trk_ttl.trk_cnt_62,1,9)),' ','') AS VARCHAR(32767)), ...

Re: Exported data without LONG VARCHAR/LONG VARBINARY enabled

Former Member — Thu, 09 Oct 2014 03:37:57 GMT

Thanks Volker Barth, you're genius. I'd tried Cast function to restrict the data output type to desired number, it's working now.

Thank you again!

Re: Exported data without LONG VARCHAR/LONG VARBINARY enabled

VolkerBarth — Thu, 09 Oct 2014 03:45:12 GMT

Well, I think the thanks should not go to me but to the SQL Anywhere team for making helpful functions like exprtype() available:) - Glad you got it working.