Re: Unknown Error '0' when using BETWEEN in a SQL statement

In article <313qa3$fsu@gagme.wwa.com> john@gagme.wwa.com (John T. Fontanilla) writes: |Hi, | Here's my setup: | COMPUTER: AT&T 3B2/522 running Unix SysV 3.2.1 | SE: 4.00.UD2 | ISQL: 4.00.UD1 | I4GL: 4.00.UH1 | | TABLE ship_info: col_name type | ship_name char(30) | bale char(10) | grain char(10) | | STATEMENT: select * from ship_info | where (bale between "1500000" and "3000000") | | PROBLEM: When I do the select statement above, I get a record | with the bale = "201359". I think I know why this happens; | I think the '15' and the '30' at the front of one million | five hundred thousand and three million are the culprits. Your columns are character types, not numeric types; therefore, you have an *alphanumeric* compare, not a numeric range test. Any shorter-length operands are left-justified and blank padded. | SOLUTIONS I TRIED: | 1. Select * from ship_info where (bale >= 1500000 and bale <= 3000000) | 2. Select * from ship_info where (bale between "1500000" and "3000000") | | PROBLEMS WITH THE TWO STATEMENTS ABOVE: | I get the Unknown error '0' (yes zero). I'm not sure if it's | a syntax problem or something. I read the manual and it says | there ... [expr] and [expr]. Hmm. If ship_info is indexed on bale, it could be bug 11761. | My questions: | 1. What's this error '0'? | 2. What's causing error '0'? | 3. Is there a way for me to get records between | one million five hundred thousand and three million WITHOUT | changing the data type of the 'bale' and 'grain' fields? | I can't change them because we use '*' if the ship does | not have a 'bale' or 'grain' capacity. Manually right-justify and pad to the left with zeroes your test values if at all possible (e.g. use "0201359" instead of just "201359"). Why couldn't you use numeric types and use <null> to denote a lack of bale or grain capacity? That's the more intuitive approach, unless you need to join on those values with another table (in which case, maybe use zero or a negative flag value?). -- Alan Denney aland@informix.com {pyramid|uunet}!infmx!aland "All you of Earth are IDIOTS!" "The greatest labor-saving device of the 20th century? Dan Rostenkowski!" -- Mr. Brain