Hi,

Can someone please explain why this BETWEEN condition
yield all records? I can't see any reasonable explanation.

SELECT *
FROM mytable
WHERE col BETWEEN '' AND 'a'


Thanks.

Hi,

as written, your predicate means >= no value and <= 'a' .

between both values are all the ascii tables values, from ascii(0)
to ascii(60), that includes 0*,1*,2*... 9* and A*,B*,C* .....Z*

This probably means all the rows of your table. This is the expected behaviour.

I would'nt write your sql this way, and to be sincere if another programmer
has to grab your code for maintenance, he will have at least a big doubt on the intentions.

First rule: when you mean 'no value', you should state
"where col IS NULL" or "WHERE col IS NOT NULL"

second rule: if your column is more that CHAR(1) long, (say CHAR(5)
) you should better state,
WHERE COL BETWEEN "00000" and "ZZZZZ"

This will be clearer for the next reader.

Or did you want to achieve something else?

Eric

Hi,

I've a fixed SQL so I'm just manipulating the values.
So, it's the second rule.

I'm still a bit unclear with it.
Since the condition is BETWEEN ascii(0) AND ascii(97),
why '0*', '1*', '2*' shows?

Why is '00000' BETWEEN ascii(0) AND ascii(97)?

'00000' = 'ascii(48) ascii(48) ascii(48) ascii(48) ascii(48)'


Please advise.

Thanks.

the right way for using "BETWEEN ascii(0) AND ascii(97)", would be to state
were column[1] between ascii(0) and ascii(97)

because the character '0' has a value of 41 in the ascii table, so it is between
ascii(0) and ascii (97). CHAR values are sorted by ascii value, position by position from left to right, so you'll have for instance:
'A'
'AA'
'ABCGGGS'
'ACA'
'ACBDFHJ'
'BC'
'BFXYU'

the value considered is not the sum of the ascii values of the string.

Let's stick to what you really want to do in your clause. Please tell me with plain words what you want in your where clause. It looks strange for me that you can have non printable CHARACTERS in a where clause. There values are rarely 'human inputtable' at least.

Hi,

My SQL is in a stored procedure for records filtering,
as such I have a fixed SQL:

SELECT *
FROM mytable
WHERE col BETWEEN param1 AND param2

When I don't want to filter anything, I can
just set param1='' and param2='a'.
It'll show all records.

When I need to filter, I just set param1 and param2
accordingly.

I managed to achieve this through trial and error.
Due to this, I'm not able to explain why it works.


Thanks.

Hi,

your method of not filtering is a bit tricky and dangerous for a code maintenance point of view. According to which version you have, you can now prepare a statement within a stored procedure. You need IDS 11.50 or above.

In your case I would do something like
Or something looking like this.

Check this excellent article from Srinivasan R. Mottupalli, IBM Informix R&D

if not possible, you'd better set param1 to ' ' ( one space ), and param2 to '~' .
this will cover all the ascii printable values.

Thanks for all the suggestion Eric.
Will try them out.