How can I find out the precision and scale information of a decimal column from the SYSCOLUMNS table?

Is it possible for me to run a query to find out the precision and scale information of a decimal column?  Thanks.
thomaszhwangAsked:
Who is Participating?
 
Kevin CrossChief Technology OfficerCommented:
Okay, found it: http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqlr.doc/sqlrmst41.htm

The collength value for a MONEY or DECIMAL (p, s) column can be calculated using the following formula:
(precision * 256) + scale

Open in new window


So, if (p * 256) + s = l ==> (l - s)/256 = p and then l - p*256 = s.
0
 
d507201Database Marketing ConsultantCommented:
I'm not an expert, but  from this page it looks like select min(fieldName), max(fieldName) might tell you what you need.

http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.odbc.doc/odbc72.htm
0
 
thomaszhwangAuthor Commented:
Sorry for the confusion.  What I meant by Precision and Scale of a Decimal field is the Precision and Scale you specified when you define a Decimal field in a table.

Now the problem is after someone else created the table, is there a way to find out the Precision and Scale information?

Thanks.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
d507201Database Marketing ConsultantCommented:
Yes, it seems like that sort of think should be captured somewhere in a system table like syscolumns, but I don't know where.  I scrolled down thru the list of system tables, looked at a few, but didn't find anything.  Seems like that sort of thing should be in syscolumns but itsn't.  

Good luck!
0
 
Kevin CrossChief Technology OfficerCommented:
In MS SQL Server, there are columns xprec and xscale, not sure what the equivalents are for Informix; however, you can try this and see if it works:

SELECT name, xprec, xscale 
FROM SYSCOLUMNS 
WHERE name = 'column_name'
;

Open in new window

0
 
thomaszhwangAuthor Commented:
If you look at the Info section in DBACCESS, you can actually see the Precision and Scale of a Decimal column, so the information should be somewhere.
0
 
thomaszhwangAuthor Commented:
@mwvisa1

No, I'm sure it's not in the SYSCOLUMNS table.  Thanks though.
0
 
Kevin CrossChief Technology OfficerCommented:
It looks like it doesn't have those columns. It appears to just store the coltype, but maybe the information is hidden in the extended values columns...I am searching to see if I spot anything relevant.

http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqlr.doc/sqlrmst41.htm

In the reference, it points to $INFORMIXDIR/incl/esql/sqltypes.h that has all the coltype codes. Maybe there is a variation on the type code based on the selected. I know the length is often calculated based on the precision/scale combination but doesn't necessarily help you get to what you want. Hmm.
0
 
thomaszhwangAuthor Commented:
What I really want is to recreate this database (schema only) on another server, if you can help with this.  Thanks.
0
 
thomaszhwangAuthor Commented:
In order to solve this equation, scale must be less than 256, is this true?
0
 
thomaszhwangAuthor Commented:
You are right.  Thanks!
0
 
Kevin CrossChief Technology OfficerCommented:
First, have you tried simply taking a backup and doing a restore? Additionally, Informix has Export and Import commands: http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.ii.doc/start/iiymgimportref.htm

Given this question is very specific to finding "precision and scale" in SYSCOLUMNS, it probably is not attracting folks with the idea that you are wanting to copy a database; therefore, a secondary recommendation would be for you to start a *new* question with that as the heading with clear details on what you are trying to do. Hopefully, the folks more versed in that will assist you if my help above doesn't help.
0
 
thomaszhwangAuthor Commented:
I have already created a separate thread.  Unfortunately I don't have permission to do an export...
0
 
Kevin CrossChief Technology OfficerCommented:
Bummer (on permissions). Hopefully you get some good responses in new thread.

Best regards and happy coding,
Kevin
0
 
Kevin CrossChief Technology OfficerCommented:
"In order to solve this equation, scale must be less than 256, is this true?" < Yes, btw. Since scale is the number of digits after the decimal, it can't be higher than precision. Precision in Informix is bound to <= 32; therefore, you can guarantee that scale is <= 32, making the formula to solve this:

MOD(l, 256) = s <== l modulus 256 ==> remainder is s (scale)
(l - s)/256 = p <== using s from above
0
 
thomaszhwangAuthor Commented:
Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.