• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 845
  • Last Modified:

Searches in Sybase: Between Clause

Hi, I am new to Sybase environment.  I am trying to write a stored proc which takes 2 parameters "From" and "To".  Then it does a "search" for records wich fall into the range specified by From and To.  I using a query which has the following basic structure:

select *
from a_table
where field1 BETWEEN From AND To

Now, if From and To are Null, that is I don't provide a value for these two parameters, I want that the query should return all records
from the database.  In this case, I set From and To to a min and max value, and that works out to be just fine.  But, if Field1 is Null for one record, I don't get a result at all.

In short, I want to get all records falling in a range if a range is provided, and if a range is not provided, I want a full table listing.

Now, to keep things simple, I just used one range here.  In my stored proc, I have about 10 ranges.

An early response would be greatly appreciated.

Thanks a lot
1 Solution

look up the isnull / ifnull functions and consider applying them to each of the field and the args. since nulls have peculiar comparision logic you can change them to empty string.

try something like this, but you will have to refine it to exactly suit your requirements

select * from a_table
where isnull( field1, '') BETWEEN From AND To.

instead of using a min & max value you can also make things more elegant by something like this

select * from a_table
where isnull( field1, '')
  BETWEEN isnull( From, '' ) AND
          isnull( to, field1, '').

but pl. check the syntax and tune for your requirements
write for more details


jychAuthor Commented:
Thanks a lot for the ideas.  I am going to try them out.

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now