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
jychAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sminniCommented:
jych

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

sminni@vsnl.com




0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jychAuthor Commented:
Thanks a lot for the ideas.  I am going to try them out.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.