Skip to main content
ExLibris
  • Subscribe by RSS
    • Back
    Aleph
    Ex Libris Knowledge Center

    Performing calculations on fields (such as z31_sum ) with CHAR datatype

    1. Last updated
    2. Save as PDF
    • Article Type: General
    • Product: Aleph
    • Product Version: 16.02

    Description:
    For ADM table z31, the datatype of Z31-SUM is CHAR(14). Is there a way I can strip out the leading zero’s and then put a dollar sign and a decimal point? Can I change this datatype to NUMBER?

    Resolution:
    You can not change the definition of this table in Oracle, but this should give you what you want.
    Instead of [select ...]

    Z31_SUM as "Amount",

    you would do:

    Z31_SUM/100 as "Amount",

    This will give you the proper decimal place.

    If you need the dollar sign, you can do:

    COLUMN TO_NUMBER(Z31_SUM)/100 FOR '$999,999,999,999.99'
    SELECT TO_NUMBER(Z31_SUM)/100 FROM Z31;

    • Article last edited: 10/8/2013