SAP SQL Anywhere

Question

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

Accepted Answer

2

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)), ...

permanent link

answered 09 Oct '14, 03:14

Volker Barth
39.3k●355●539●809
accept rate: 33%

edited 09 Oct '14, 03:15

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!

(09 Oct '14, 03:37) ctlavender

Replies hidden

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.

(09 Oct '14, 03:45) Volker Barth

flat view

SAP SQL Anywhere

Exported data without LONG VARCHAR/LONG VARBINARY enabled

Follow this question

Related questions