Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-09-13
16
Medium Priority
?
783 Views
Last Modified: 2013-12-24
Is it possible for me to run a query to find out the precision and scale information of a decimal column?  Thanks.
0
Comment
Question by:thomaszhwang
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 2
16 Comments
 
LVL 7

Expert Comment

by:d507201
ID: 36532743
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
 

Author Comment

by:thomaszhwang
ID: 36532799
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
 
LVL 7

Expert Comment

by:d507201
ID: 36532865
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36532873
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
 

Author Comment

by:thomaszhwang
ID: 36532877
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
 

Author Comment

by:thomaszhwang
ID: 36532899
@mwvisa1

No, I'm sure it's not in the SYSCOLUMNS table.  Thanks though.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36532910
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
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 36532934
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
 

Author Comment

by:thomaszhwang
ID: 36532935
What I really want is to recreate this database (schema only) on another server, if you can help with this.  Thanks.
0
 

Author Comment

by:thomaszhwang
ID: 36532946
In order to solve this equation, scale must be less than 256, is this true?
0
 

Author Closing Comment

by:thomaszhwang
ID: 36532980
You are right.  Thanks!
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36532997
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
 

Author Comment

by:thomaszhwang
ID: 36533002
I have already created a separate thread.  Unfortunately I don't have permission to do an export...
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36533008
Bummer (on permissions). Hopefully you get some good responses in new thread.

Best regards and happy coding,
Kevin
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36533086
"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
 

Author Comment

by:thomaszhwang
ID: 36533601
Thanks.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

715 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