I am writing an application that has to either communicate with DB2 or
MySQL. I am having trouble coming up with a query that casts a column
to integer (or real but I currently only need int) that can be written
the same way in both databases. I can write a query that works in
either one, but it seems there should be an ANSI standard way to write
the query that should work in both (or all) databases.

Here is the problem. I have a character field that can contain
numeric or string data, so it must be a character field. If I do a
compare against the field directly, I get an alpha compare vs a
numeric compare. So '10' is < '2', etc... For the numeric fields, I
would like to do a ranged where clause. For instance:

works in DB2:
select a from table where integer(a) > 8 and integer(a) < 10
select a from table where cast(a as integer) > 8 and cast(a as
integer) < 10

works in MySQL:
select a from table where 0 + a > 8 and 0 + a < 10
select a from table where cast(a as unsigned) > 8 and cast(a as
unsigned) < 10

I think MySQL is doing the ANSI standard thing with the cast function.

Is there any cross database SQL for doing this type of query?

There are some alternate solutions of course including: making the
program aware of which type of database I'm using and altering the
query to match, and left filling numeric data with spaces or zeros.
But the first solution means non-transportable queries and the second
complicates the use of the data. Any ideas?

The world rejoiced as dougstave (AT) charter (DOT) net (Doug Stave) wrote:
No, it is not. UNSIGNED is not an ANSI SQL standard type, so what you
are doing is not portable.
--
(format nil "~S@~S" "cbbrowne" "ntlug.org")
http://www3.sympatico.ca/cbbrowne/emacs.html
I'M SORRY, LUSER, I CAN'T LET YOU DO THAT. WHY DON'T YOU LIE DOWN AND
TAKE A STRESS PILL? MY NAME IS LM1. I WAS MADE AT THE LISP MACHINE
FACTORY IN MASSACHUSETTS ON DECEMBER 12, 1992. MY TEACHER WAS MR.
WINSTON. HE TAUGHT ME A PROGRAM. WOULD YOU LIKE TO SEE IT? HERE IT
IS:

dougstave (AT) charter (DOT) net (Doug Stave) wrote in message news:<fee1598c.0310290904.3257e654 (AT) posting (DOT) google.com>...

You should use WHERE CAST(a AS INTEGER) > 8 etc. to be ANSI compliant.
Does mySQL not support BETWEEN? Or just = 9 in this particular case.

Yuk! I'm not sure that implicit casting is covered by ANSI???

I don't. There is an ANSI INTEGER datatype, but I've never heard of an
UNSIGNED one.


DG