Forum Stats

  • 3,838,564 Users
  • 2,262,383 Discussions
  • 7,900,687 Comments

Discussions

DB2 SQL to Oracle SQL change

User_49B97
User_49B97 Member Posts: 4 Red Ribbon

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??

Tagged:

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,243 Red Diamond
    edited Jul 30, 2022 6:26PM

    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
  • User_49B97
    User_49B97 Member Posts: 4 Red Ribbon

    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
    Frank Kulash Member, Moderator Posts: 42,243 Red Diamond

    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.

    User_49B97