Informix

nested-group-icon.png

DB2

Expand all | Collapse all

How could I retrieve the decimal precision and scale values?

  • 1.  How could I retrieve the decimal precision and scale values?

    Posted 6 days ago

    Hello All,
    Is there a catalog table to find the precision and scale values ​​of decimal type?
    syscolumns has only collength values.

    Thanks,



    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------


  • 2.  RE: How could I retrieve the decimal precision and scale values?

    Posted 6 days ago
    No, there isn't a table to do that.  You have to perform a calculation using the value in collength.  Treated as a 16-bit value, the MSB (most significant byte) records the first number in DECIMAL(n,m), and the LSB (least significant byte) records the second number, with the caveat that a floating-point number (DECIMAL(16), for example, in a database that is not MODE ANSI) has 0xFF (255) as the LSB.

    Using HEX(collength) on decimal or money columns should confirm this:

    SELECT t.tabid, t.tabname, c.colno, c.colname, c.coltype, c.collength, HEX(c.collength)
      FROM "informix".systables AS t
      JOIN "informix".syscolumns AS c ON t.tabid = c.tabid
     WHERE c.coltype IN (5, 8, 261, 264);

    --
    Jonathan Leffler <jonathan.leffler@gmail.com>  #include <disclaimer.h>
    Guardian of DBD::Informix - v2018.1031 - http://dbi.perl.org
    "Blessed are we who can laugh at ourselves, for we shall never cease to be amused."





  • 3.  RE: How could I retrieve the decimal precision and scale values?

    Posted 6 days ago
    @Jonathan Leffler
    Thanks for the reply.
    The sample query was also a good reference. ​​

    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------



  • 4.  RE: How could I retrieve the decimal precision and scale values?

    Posted 6 days ago
    SangGyu:

    That information is embedded in the sqllength column of the syscolumns row for the column. See the macros in $INFORMIXDIR/incl/esql/decimal.h:

    #define PRECTOT(x)      (((x)>>8) & 0xff)
    #define PRECDEC(x)      ((x) & 0xff)

    The low order 8 bits is the precision the the high order 8 bits are the number of digits.

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 5.  RE: How could I retrieve the decimal precision and scale values?

    Posted 5 days ago
    @Art Kagel
    I am always surprised by your knowledge. Thank you for the detailed explanation.

    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------



  • 6.  RE: How could I retrieve the decimal precision and scale values?

    Posted 5 days ago
    Edited by SangGyu Jeong 5 days ago
    @Art Kagel
    Oh, I have one more question. Can I use the bit shift operator (>>, <<) function in Informix?
    Should I make my own function..?

    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------



  • 7.  RE: How could I retrieve the decimal precision and scale values?

    Posted 5 days ago
    Edited by Andreas Legner 5 days ago
    SELECT t.tabid, t.tabname, c.colno, c.colname, c.coltype, c.collength, HEX(c.collength),
           decode(bitand(c.coltype, "0xff"), 5, "decimal", 8, "money", "<...>") || "(" ||
            ifx_bit_rightshift(c.collength, 8) || "," || bitand(c.collength, "0xff") || ")"
            as type
      FROM "informix".systables AS t
      JOIN "informix".syscolumns AS c ON t.tabid = c.tabid
     WHERE c.coltype IN (5, 8, 261, 264);​


    Some tweaking still required...



  • 8.  RE: How could I retrieve the decimal precision and scale values?

    Posted 5 days ago
    @Andreas Legner
    Is it a hidden feature? There are still many unknown worlds.. ;)​​

    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------



  • 9.  RE: How could I retrieve the decimal precision and scale values?

    Posted 5 days ago
    If missing documentation qualifies as hiding ;-)
    I'll log a doc defect to get this corrected...

    ------------------------------
    Andreas Legner
    ------------------------------



  • 10.  RE: How could I retrieve the decimal precision and scale values?

    Posted 5 days ago
    This is what I get for answering a question before 6AM and before I have my coffee. Of course, as Jonathan noted, the column that contains the encoded details you need is syscolumns.collength not sqllength.

    SangGyu: Thank you, but the surprising thing would be if after doing this for 38+ years I hadn't learned a thing or three.  B^)

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------