Re: SQL Question???
I get a -305 error using 4.10 Informix:
305: Subscripted column (num1) is not of type CHAR, VARCHAR, TEXT nor BYTES
Richard Thomas writes:
* Hi all,
*
*
* What's wrong with:
*
* select * from tab1 where column1[4,5] =3D "87"
*
* Simple :-)
*
* I'll admit I've never heard of "mod", but the above
* solution works fine for me!
*
* Cheers,
*
* Richard.
*
*
* ----------------------------------------------------------------- =20
* | _ =AF\\ | Richard Thomas |
* | \\ 0_ | r.thomas@csl.gov.uk |
* | \\ / | |
* | oo/ | "20 Regal and a four-pack, |
* | / \\ | I guess I'm set for the night" |
* | \\_/ | - "TV Tan" The Wildhearts |
* -----------------------------------------------------------------
*
*
* } From ilist@rmy.emory.edu Thu Oct 19 03:33:43 1995
* } From: proberts@lynx.informix.com (Paul Roberts)
* } Subject: Re: SQL Question???
* } Date: 18 Oct 1995 21:30:10 GMT
* } To: informix-list@rmy.emory.edu
* } X-Informix-List-To: rt10hp@csl.gov.uk
* } X-Informix-List-Id: <news.18044>
* }=20
* } In article <463ihb$q72@ixnews7.ix.netcom.com>,
* } Mark Truty <marktrut@ix.netcom.com> wrote:
* } >
* } >I am trying to do a SQL query on a numeric field.
* } >How do I query the last 2 numbers of a 5 digit field?
* } >Example: number field =3D 54387 - I want to search for all records =
* that
* } >end in 87.
* } >Thanks
* }=20
* }=20
* } I really feel that we ought to be able to say:
* }=20
* } select *
* } from tab1
* } where column1 mod 100 =3D 87
* }=20
* } But last time I looked, the "mod" function was available in 4GL but=20
* } not in SQL.
* }=20
* } Here's a horribly clunky solution (which I present only in order to
* } encourage others to present their far more elegant solutions. That's
* } my story and I'm sticking to it):
* }=20
* } select tab1.*, "xxxxxxxx" dummy_field
* } from tab1
* } into temp t1 with no log ;
* }=20
* } update t1 set dummy_field =3D column1 ;
* }=20
* } [Or explicitly create a temp table "t1" with a character=20
* } column "dummy_field" in place of tab1's integer column=20
* } "column1" and then:
* }=20
* } insert into t1 select * from tab1
* }=20
* } I hate typing create table statements, so I do it as =
* above]
* }=20
* } select * from t1 where dummy_field matches "*87"
* }=20
* } - Paul
* }=20
Robert Minter Data Systems Support \\\\\\_///
Senior Software Engineer A Client Technologies Company ( _ _ )
E-Mail: rob@dssmktg.com Tel: 714.771.0454 (| ^ |)
#include <disclaimer.h> Fax: 714.771.3028 \\`-'/
De Colores - Emmaus OC-13 SURF'S UP \\_/