The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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.3k287438645
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: 334 times

last updated: 09 Oct '14, 03:45