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

asked 09 Oct '14, 00:59

ctlavender's gravatar image

ctlavender
1094410
accept rate: 0%

edited 09 Oct '14, 01:00


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%20Barth's gravatar image

Volker Barth
29.6k294444650
accept rate: 32%

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×10

question asked: 09 Oct '14, 00:59

question was seen: 349 times

last updated: 09 Oct '14, 03:45