Database

turn on suggestions

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

Showing results for

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

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

04-13-2007
10:19 AM

04-13-2007
10:19 AM

Hi,

i am fireing same query in oracle and in teradata on same table( source and target tables).

when i am fireing the query in oracle i am getting values like

A

45.98

B

96.69

C

97.06

but when i am fireing the same query in teradata i am getting values like this

A

46.00

B

96.70

C

97.10

I want the same values in teradata also.

this is the query i am using in both the databases.

SELECT

ROOM_id,

SUM(CLOCK_COUNT)/SUM(MONTH_COUNT)*100 AS A,

SUM(CLOCK_UPTIME_COUNT)/SUM(MONTH_COUNT)*100 AS B,

SUM(CONTRACT_HOUR_COUNT)/SUM(CONTRACT_COUNT)*100 AS C

FROM

TABLE NAME

Group By 1

kindly help me to resolve the issue ...

thxs,

Moras

i am fireing same query in oracle and in teradata on same table( source and target tables).

when i am fireing the query in oracle i am getting values like

A

45.98

B

96.69

C

97.06

but when i am fireing the same query in teradata i am getting values like this

A

46.00

B

96.70

C

97.10

I want the same values in teradata also.

this is the query i am using in both the databases.

SELECT

ROOM_id,

SUM(CLOCK_COUNT)/SUM(MONTH_COUNT)*100 AS A,

SUM(CLOCK_UPTIME_COUNT)/SUM(MONTH_COUNT)*100 AS B,

SUM(CONTRACT_HOUR_COUNT)/SUM(CONTRACT_COUNT)*100 AS C

FROM

TABLE NAME

Group By 1

kindly help me to resolve the issue ...

thxs,

Moras

7 REPLIES

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

04-13-2007
01:02 PM

04-13-2007
01:02 PM

It certainly comes from the TYPE of your columns...

INTEGER has no decimals and the result of DECIMAL(x, m) / DECIMAL(y, n) is DECIMAL(y, max(m,n))...

So if you want a DECIMAL(z,2) result, CAST one of your values to DECIMAL(z,2)...

INTEGER has no decimals and the result of DECIMAL(x, m) / DECIMAL(y, n) is DECIMAL(y, max(m,n))...

So if you want a DECIMAL(z,2) result, CAST one of your values to DECIMAL(z,2)...

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

04-15-2007
01:33 AM

04-15-2007
01:33 AM

Hi,

i have tried with casting on of the decimals as (z,3) but i am getting the same results, the result is rounding off, is there any other way to get the result without rounding off.

i have tried with casting on of the decimals as (z,3) but i am getting the same results, the result is rounding off, is there any other way to get the result without rounding off.

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

04-16-2007
12:36 AM

04-16-2007
12:36 AM

I presume you are querying from queryman. in that case go to tools->options and in the answerset tab look from something like "number of decimal places to display for float".

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

04-25-2007
09:43 AM

04-25-2007
09:43 AM

Looks to me like the problem is your * 100 at the end. The casting to decimal(x,3) or decimal(x,2) is giving 2 decimal precision prior to the multiplication. Also, if one of the two part to the division equation = float, the result will = float data type. cast both parts of the division equation separately, and then you will get the appropriate answer.

ie

cast(number1 as decimal(18,4)) / cast(number2 as decimal(18,4)) * 100

In this case, the result of the division problem will have a precision of 4 decimal places. When multiplying by 100 you are basically shifting the precision 2 places to the left with a result of 2 decimal precision. If you don't wish to see the trailing zeros wrap the whole thing in another cast( as decimal(18,2))

ie

cast(number1 as decimal(18,4)) / cast(number2 as decimal(18,4)) * 100

In this case, the result of the division problem will have a precision of 4 decimal places. When multiplying by 100 you are basically shifting the precision 2 places to the left with a result of 2 decimal precision. If you don't wish to see the trailing zeros wrap the whole thing in another cast( as decimal(18,2))

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

04-25-2007
12:47 PM

04-25-2007
12:47 PM

Hi Morashi,

according to Standard SQL any calculation involving decimals are rounded after each step, so simply reorder the calculations:

SELECT

ROOM_id,

100*SUM(CLOCK_COUNT)/SUM(MONTH_COUNT) AS A,

100*(CLOCK_UPTIME_COUNT)/SUM(MONTH_COUNT) AS B,

100*SUM(CONTRACT_HOUR_COUNT)/SUM(CONTRACT_COUNT) AS C

FROM

TABLE NAME

Group By 1

Dieter

according to Standard SQL any calculation involving decimals are rounded after each step, so simply reorder the calculations:

SELECT

ROOM_id,

100*SUM(CLOCK_COUNT)/SUM(MONTH_COUNT) AS A,

100*(CLOCK_UPTIME_COUNT)/SUM(MONTH_COUNT) AS B,

100*SUM(CONTRACT_HOUR_COUNT)/SUM(CONTRACT_COUNT) AS C

FROM

TABLE NAME

Group By 1

Dieter

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

10-11-2016
10:02 AM

10-11-2016
10:02 AM

Hi Dieter,

Does the 100 need to be multiplied to the left instead of right to avoid the rounding at the tens in the example below?

Regards,

Srini

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

10-11-2016
10:52 AM

10-11-2016
10:52 AM

Yep, the rule of thum is: *Multiply first, then divide*

Copyright © 2004-2015 Teradata Corporation. Your use of this Teradata website is governed by the Privacy Policy and the Terms of Use, including your rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.

The Privacy Policy and Terms of Use for this Teradata website changed effective September 8, 2016.