Re: Informix SQL query on INTEGER column

In article <2063@das13.snide.com> dave@das13.snide.com (Dave Snyder) writes: >In article <1993Jul29.203531.7451@porthos.cc.bellcore.com> ggeorge@polo.NoSubdomain.NoDomain (Greg George) writes: >} I'm trying to do an informix SQL SELECT on an INTEGER column to get >} only the first 3 digits of the number. For example, if i have >} the following table: >} >} CREATE TABLE t1 >} ( field1 INTEGER, >} field2 CHAR(10) ); >} >} Now, if I have 12345 in field1, how can I do a select on the table >} and only display the first 3 digits (ie: 123). >} >Have you tried dividing the field by 100 before displaying it? > >DAS >-- >David Snyder @ Snide Computer Services - Folcroft, PA Current Release > is db4glgen-3.15 >UUCP: ..!uunet!das13!dave INTERNET: dave.snyder@snide.com The main problem with dividing by 100 is that the number may be bigger than 99,999, in which case, you would get too many digits still. (ie. 123456 / 100 = 1234) In order to get the division idea to work, a simple WHILE statement could be used such as the following: WHILE x > 999 LET x = x / 10 END WHILE Of course, this would require selecting into temporary variables, and displaying the temporary variable, and thus wouldn't be all that cute in the long run (especially considering large amounts of numbers to be selected). I would try selecting it into a temporary table changing it on the way into a character field, and doing a select field[1,3] on the new table. Or something like that. john... _ \\__ /( __> //| \\ ///___) Shadowfax... -- john... From: jcfowler@david.wheaton.edu (Shadowfax...) _ \\__ /( __> //| \\ ///___) Shadowfax...