How could I retrieve the decimal precision and scale values?
SangGyu Jeong asked how to get decimal precision and scale, since syscolumns has only collength. Jonathan Leffler explained collength encodes them: high byte is the first number, low byte the second, with 255 as low byte for floating decimals. Art Kagel cited decimal.h macros and corrected sqllength to collength. Andreas Legner posted a query using ifx_bit_rightshift and bitand, and said he would log a documentation defect.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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 ------------------------------ #Informix
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."
Thanks for the reply. The sample query was also a good reference. ------------------------------ SangGyu Jeong Software Engineer Infrasoft Seoul Korea, Republic of ------------------------------
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 ------------------------------
@Art Kagel I am always surprised by your knowledge. Thank you for the detailed explanation. ------------------------------ SangGyu Jeong Software Engineer Infrasoft Seoul Korea, Republic of ------------------------------
@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 ------------------------------
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 ------------------------------
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...
#Informix
@Andreas Legner Is it a hidden feature? There are still many unknown worlds.. ;) ------------------------------ SangGyu Jeong Software Engineer Infrasoft Seoul Korea, Republic of ------------------------------
If missing documentation qualifies as hiding ;-) I'll log a doc defect to get this corrected... ------------------------------ Andreas Legner ------------------------------