Hello, everybody!

I've encountered a problem with implicit datatype

conversion. I have a result table T1 defined as follows

create table T1 (col1 decimal(21,6) null).

Also i have source table defined as follows

create table S1 (col1 decimal(18,6) null).

The problem appears when i perform this operation

update T1

set T1.col1 = S1.col1 / 30

Then i receive message "Truncation error occured." Numbers

in S1 are trivially smaller than datatype decimal(21,6) able

to save.

I've read about implicit conversions of datatypes during

arithmetic operations, but there is nothing said about to

what decimal datatype int is converted (default (18,0) or to

the (18,6)). Besides when i perform addition, subtraction

and multiplication they are done without erros. So the

questions are:

1.What's wrong with division?

2.To what exactly datatype integer will be converted?

3.Will be there any conversions between two equal decimals

according to sybase formula, say (18,6)?

4.Why are addition, subtraction and multiplication performed

well and division not?

P.S. I know that this will work

update T1

set T1.col1 = convert(decimal(21,6),(S1.col1 / 30))

but the questions exist.

Will such conversion harm performance?

I work with ASE 12.5.4 64-bit.

Thanks.

I've understood what's wrong with division.

When dividing on some integers you get answer with period in

decimal fraction (e.g.

3.373737373737373737373737373737373737)

and we get scale overflow. But questions about implicit

conversions still need answers:).

On 18 Sep 2009 06:52:29 -0700, alexander wrote:

google "implicit conversion site:sybase.com" or alternatively look in

Chapter 2. Transact Sql- Functions of TSQL guide for a discussion and

conversion table. I think it might be appropriate to make or check on

feature requests if you think an appropriate conversion is not

"implicit".

Jay

Hi, Jay.

Thanks for answer.

But it's not what I need. I've read TSQL users guide as well

as reference manual. And there is nothing said about

implicit conversions in chapters you've mentioned. I've read

how determine precision and scale of a result when you

perform arithmetic operations between different decimals.

But what about integers? There is only a table indicating

possibility of implicit conversion between different

datatypes in the reference manual.

But what decimal will I get when I perform multiplication

between decimal(15,5) and int (for example). Will be int

converted to decimal(18,0) as default difinition of decimal

or to decimal(15,5)?

When you perform this

a=b/c

you get implicit conversions.

When you perform this

a=convert(b/c)

you also get implicit conversion of b/c and then the result

explicitly converted to the datatype you need.

1> select @@version as "ASE_Version"

2> , convert(decimal(15,5),1) * convert(int,1) as "Num_times_Int"

3> , convert(decimal(15,5),1) / convert(int,1) as "Num_divide_Int"

4> into #tmp

5> select * from #tmp

6> exec tempdb..sp_help #tmp

7> drop table #tmp

8>

9> go

(1 row affected)

ASE_Version

Num_times_Int Num_divide_Int

----------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------

-------------------- --------------------

Adaptive Server Enterprise/15.0.3/EBF 16743 ESD#2/P/Linux

Intel/Linux 2.6.9-42.ELsmp i686/ase1503/2707/32-bit/FBO/Sun Jul 26

23:03:48 2009

1.000000 1.000000

(1 row affected)

Name Owner Object_type Create_date

--------------------- ----- ----------- -------------------

#tmp00000130018514529 dbo user table Sep 18 2009 5:13PM

Column_name Type Length Prec Scale Nulls Default_name Rule_name

Access_Rule_name Computed_Column_object Identity

-------------- ------- ------ ---- ----- ----- ------------ --------- ----------------

---------------------- ----------

ASE_Version varchar 255 NULL NULL 1 NULL NULL NULL

NULL 0

Num_times_Int decimal 12 26 5 0 NULL NULL NULL

NULL 0

Num_divide_Int decimal 12 26 16 0 NULL NULL NULL

NULL 0

Object does not have any indexes.

Thanks, Kevin!

On 18 Sep 2009 14:15:56 -0700, alexander wrote:

Sorry. I was on vacation. I see your problem is a bit different from

my assumptions. Glad you got some help. I had assumed you wanted an

implicit conversion that was not available (my bad!).

Jay