Database

turn on suggestions

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

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*