Error -1213 : Character to numeric conversion failed

I recently upgraded from IDS 7.23 to 7.30. Database queries that used to work now fail with Error -1213. An example query is as follows: SELECT UNIQUE TRIM(LEADING '0' FROM CSKS.KOSTL), <other fields> FROM <various tables> WHERE <various criteria> AND CSKS.KOSTL < 10000 I have abridged the query because the fragment CSKS.KOSTL < 10000 is the element of interest. Field CSKS.KOSTL is type CHAR(10), and I am comparing it to an integer value of 10000. I understand that the TRIM function returns a type of VARCHAR. I have reviewed table CSKS for non numeric data, blanks, etc, and found it to be clean. I then tried casting 10000 to a character string by placing it in single quotes (CSKS.KOSTL < '10000') and now my query works. Has anybody else out there experienced this? As I said, this all worked fine up to 7.23, but in 7.3 it fails. Can anybody explain what I have done wrong ( And perhaps just been lucky up til this point )? Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.

We experienced this very problem after porting to 7.3. In my mind your not really doing anything wrong. It just seems that the 7.3 engine is not as forgiving when doing cross type comparisons. Unfortunately, most of us had to find out the hard way. Good Luck Ken In article <7ke3l6$otb$1@nnrp1.deja.com>, dfolts@my-deja.com wrote: > I recently upgraded from IDS 7.23 to 7.30. Database queries that used > to work now fail with Error -1213. An example query is as follows: > > SELECT UNIQUE TRIM(LEADING '0' FROM CSKS.KOSTL), <other fields> > FROM <various tables> > WHERE <various criteria> > AND CSKS.KOSTL < 10000> > I have abridged the query because the fragment CSKS.KOSTL < 10000 is > the element of interest. Field CSKS.KOSTL is type CHAR(10), and I am > comparing it to an integer value of 10000. I understand that the TRIM > function returns a type of VARCHAR. I have reviewed table CSKS for non > numeric data, blanks, etc, and found it to be clean. I then tried > casting 10000 to a character string by placing it in single quotes > (CSKS.KOSTL < '10000') and now my query works. > > Has anybody else out there experienced this? As I said, this all worked > fine up to 7.23, but in 7.3 it fails. Can anybody explain what I have > done wrong ( And perhaps just been lucky up til this point )? > > Sent via Deja.com http://www.deja.com/ > Share what you know. Learn what you don't. > Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.