Database

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-03-2013
01:37 AM

06-03-2013
01:37 AM

Hi, I have doubt about the sql statements below:

sel cast(400.000000000 * 100000000 as decimal(18,2)) ---OK

sel cast(400.000000000 * 100000000 as decimal(38,8)) ---error,overflow...

why???

thks!

12 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-03-2013
02:30 AM

06-03-2013
02:30 AM

Hi,

When I checked the same, am not getting numeric overflow error.

sel cast(400.000000000 * 100000000 as decimal(38,8));

*** Query completed. One row found. One column returned.

*** Total elapsed time was 1 second.

(400.000000000*100000000)

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

40000000000.00000000

Priyanka!!

When I checked the same, am not getting numeric overflow error.

sel cast(400.000000000 * 100000000 as decimal(38,8));

*** Query completed. One row found. One column returned.

*** Total elapsed time was 1 second.

(400.000000000*100000000)

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

40000000000.00000000

Priyanka!!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-03-2013
03:05 AM

06-03-2013
03:05 AM

The default maximum number of decimal digits for an expression is based on dbscontrol General field 13: MaxDecimal

When you submit a

sel type(400.000000000 * 100000000)

you'll probably get dec(15,9) or dec(18,9).

The best solution would be changing MaxDecimal to 38, but this has do be done by your DBA after checking for possible side-effects for existing applications (and it requires a restart). On the other hand this is usually safe, when a query/application encountered this error it's already doing a workaround:

sel cast(400.000000000 as decimal(38,8)) * 100000000;

Dieter

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-03-2013
03:24 AM

06-03-2013
03:24 AM

Dieter:

decimal(38,8) is bigger than decimal(18,2),why it overflow?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-03-2013
03:32 AM

06-03-2013
03:32 AM

dieter:

when i excute sql as bellow:

sel type(400.000000000 * 100000000),

result is decimal(18,9).

thks

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-03-2013
03:42 AM

06-03-2013
03:42 AM

You multiply first, this exceeds the dec(18,9) and thus fails before the CAST.

So CAST one of the operands before the multiplication.

Dieter

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-03-2013
03:50 AM

06-03-2013
03:50 AM

dieter:

When cast to deciaml(18,2),it's ok?

I'm so puzzle with this....

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-03-2013
04:14 AM

06-03-2013
04:14 AM

Of course it's ok, simply try it.

Base the definition on your actual datatypes/data/requirements.

Dieter

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-03-2013
06:20 PM

06-03-2013
06:20 PM

Dieter:

I do know this is ok and I have test it.But why?

When cast to decimal(38,8),you say cast should be excuted after the multiplication.

So, when cast to decimal(18,2), I think that multiplication should be excuted first.

Why it is ok?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-03-2013
11:42 PM

06-03-2013
11:42 PM

cast(400.000000000 * 100000000 as decimal(38,8))

--> multiplication first, maximum number of digits for the result of the calculationbased on MAXDECIMAL: 18 (fails ), then CAST

cast(400.000000000 as decimal(38,8)) * 100000000

--> CAST first to 38 digits, then multiplication (success)

Same for any cast to a decimal with sufficient precision like decimal(18,2)

Dieter