DB2 V7 MVS - Decimal Column Insertion Truncate or Round

Posted on 2004-10-25
Last Modified: 2011-09-20
The standard option for insertion to a decimal column is (appears) to be truncate  
is there anyway to alter this to round?


A Int
B Decimal(6,2)

insert into mytab (A,B)
Values (1,1.009)

results in     A=1 , B=1.00

or do i need to specify      
insert into mytab (A,B)
Values (1,round(1.009,2))

which does give     A=1 , B=1.01


Question by:Lowfatspread
    LVL 13

    Accepted Solution

    either define the column as decimal 6,3, no truncation will take place or yes, use the round function, but why would you want to round the decimal number if it has a precision of 3?
    Usually, rounding takes place in the select statement, not in the insert
    LVL 50

    Author Comment

    thanks ghp7000

    unfortunately I'm working developer who are arms length from a package which
    has some "wonderful" rules for data types etc...

    there input source is from a business who rely on excell spreadsheets and don't
    understand that a displayed value isn't necessarily the same as the value that gets

    so there's politics involved...

    i was just wondering if an option existed.

    LVL 13

    Expert Comment

    not that im flogging a dead horse, but perhaps I misunderstood your question-if the input source has a precision of 3, then presumably the customer wants that level of precision (they input numbers into their excel spreadsheet using a precision of 3), so why would you want to change the value that would be displayed? or change the value that is inserted into the db? I"m thinking the only reason you would want to do this is perhaps because the form doesn't support a field that has a precision of 3, in which case you should have the form changed?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Shellfire Box VPN + Lifetime Subscription

    The Shellfire Box easily connects all of your devices, even those that don't offer the possibility to establish a safe vpn connection. Access blocked content and surf safely, no matter where in the world you are located.

    November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
    Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    846 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now