DB2 SQL to Oracle SQL change

User_49B97

Can someone please confirm syntax db2 vs oracle just want to make sure it's correct

I don't know db2 syntax that well (or not much at all) additionally, I don't have ability to check db2 syntax.

DB2 syntax:

SELECT DECIMAL(123245632134.1234567 ,15, 3) FROM TABLE;

retuns (I think)

123245632134.123 <-- NUMBER

Oracle syntax:

SELECT TO_NUMBER(TO_CHAR(123245632134.1234567,'999999999999.999')) FROM TABLE;

returns

 123245632134.123 <-- NUMBER

Oracle syntax return number correctly however for me this looks weird, is there any other shorter way to present DB2 decimal function Decimal(num,15, 3) in Oracle SQL??

Frank Kulash

Hi, @User_49B97

is there any other shorter way

Yes: whenever you're tempted to nest one conversion function inside another (such as TO_CHAR inside TO_NUMBER), it's smart to look for a simpler way. In this case:

CAST (n AS NUMBER (15, 3))

where n is a NUMBER, such as 123245632134.1234567. A slightly different way is

ROUND (n, 3)

the difference is that CAST will raise an error if ABS (n) >= 1E12, but ROUND will accept any value.

User_49B97

so the cast will raise an error ... however for following number 123245632134.1236567 , see that 4th decimal value is 6 ... all 3 functions will round up (see sqls below).

SELECT TO_NUMBER(TO_CHAR(123245632134.1236567,'999999999999.999')) FROM DUAL;

SELECT CAST (123245632134.1236567 AS NUMBER (15, 3)) FROM DUAL;

SELECT ROUND (123245632134.1236567, 3) FROM DUAL;

Do you know if SELECT DECIMAL(123245632134.1236567 ,15, 3) FROM TABLE; in DB2 SQL would round up or will it just cut it off 123245632134.123 or will it round up just like oracle functions, if the 4th decimal place digit is greater than 5?

123245632134.124

Frank Kulash

Hi, @User_49B97

Do you know if SELECT DECIMAL(123245632134.1236567 ,15, 3) FROM TABLE; in DB2 SQL would round up or will it just cut it off 123245632134.123 or will it round up just like oracle functions, if the 4th decimal place digit is greater than 5?

Sorry, I've never used DB2. The IBM documentation

DECIMAL or DEC scalar function - IBM Documentation

says it will truncate (i.e., ignore extra digits after the decimal point), so .1236567 results in .123, but the Oracle functions I suggested earlier round to the nearest acceptable value, so .1236567 results in .124. You can use

CAST (TRUNC (n, 3) AS NUMBER (15, 3))

or

TRUNC (n, 3)

to truncate in Oracle.