Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2004
    Posts
    5

    Unanswered: Empty from value for BETWEEN condition

    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.

  2. #2
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    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

  3. #3
    Join Date
    Sep 2004
    Posts
    5
    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.

  4. #4
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    the right way for using "BETWEEN ascii(0) AND ascii(97)", would be to state
    were column[1] between ascii(0) and ascii(97)

    why '0*', '1*', '2*' shows?
    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.

  5. #5
    Join Date
    Sep 2004
    Posts
    5
    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.

  6. #6
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    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
    Code:
    IF param1 IS NULL THEN
        LET statement = "SELECT * FROM tablename";
    ELSE
        LET statement = "SELECT * FROM tablename WHERE col1 BETWEEN  " || param1 || "AND " || param2 ;
    END iF
    
    PREPARE prep_stmt FROm statement;
    DECLARE mycursor FROM prep_stmt ;
    OPEN CURSOR mycursor ;
    while (1)
        fetch cursor..... ;
    end while
    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.

  7. #7
    Join Date
    Sep 2004
    Posts
    5
    Thanks for all the suggestion Eric.
    Will try them out.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •