how to change datatype of a column in sybase query?

Question

One of my query to Sybase server is returning garbage data. After some investigations i found out that one of the columns with datatype double is causing the issue. If I don't select that particular column then the query returns correct result. The column is question is a double with laarge number of decimal places. I tried to use round function upto 4 decimal places but still i get corrupt data. How can I correctly specify the column in my query to get correct data?

I am using windows 7 box and Sybase Adaptive server enterprise driver. (Sybase client 15.5). I am using 32 bit drivers.

Sample results:

Incorrect result using sybase ASE driver on windows 7 box

"select ric_code as ric, adjusted_weight as adjweight from v_temp_idx_comp where index_ric_code='.AXJO' and ric_code='AQG.AX'"

 ric adjweight
 1 AQG.AX        NA
 2   \020        NA
 3   <NA>        NA

Correct result on windows xp box using Merant driver

 "select ric_code, adjusted_weight from v_temp_idx_comp where index_ric_code='.AXJO' and ric_code='AQG.AX'"

 ric_code adjusted_weight
 1   AQG.AX    0.3163873547

Regards, Alok

Give me an example of the result.

aF.
– aF.

2012-05-08 10:14:24 +00:00
Commented May 8, 2012 at 10:14 — aF.
– aF., Commented May 8, 2012 at 10:14

aF. · Accepted Answer · 2012-05-08 10:30:34Z

1

You may try convert to numeric like this:

select ric_code as ric, weight, convert(numeric(16,4), adjusted_weight) as adjweight, currency as currency 
from v_temp_idx_comp 
where index_ric_code='.AXJO'

answered May 8, 2012 at 10:30

aF.

67k45 gold badges141 silver badges200 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Alok Over a year ago

This did not work. I got error 1] "ZZZZZ 2715 [Sybase][ODBC Driver][Adaptive Server Enterprise]Can't find type 'double'."

Alok Over a year ago

yes, numeric solution worked fine. thanks. i am going to mark this as answer.

Alok Over a year ago

Though i am able to make my query work, but this is not a perfect solution for me. such queries are used in many places and not practical to change them all. when we moved to new machine, we were forced to use new drivers. i am going to move back to old driver :)

aF. Over a year ago

Roger that! Be aware to the precision and scale of the numeric.

Collectives™ on Stack Overflow

how to change datatype of a column in sybase query?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related