1

Need to get the data from DB based on the a column and the range is provided.
I have a student table with ID in it.
when i query the table on given range of id i.e between 10-20 then it is returning correct results.
ID is Varchar

SELECT * 
  FROM student 
 WHERE id BETWEEN 'AB10' AND 'AB20'

but lets suppose one of the range is not provided as range
1. ID from empty
2. ID to AB20
then ideally it should return from starting one to the provided id but i am getting no rows. My query is 

SELECT * 
  FROM student 
 WHERE id BETWEEN 'null' AND 'AB20'
Alexander
  • 3,129
  • 2
  • 19
  • 33
user956021
  • 163
  • 1
  • 1
  • 12
  • 2
    Your query doesn't make sense. You are assuming some sort of order to the table that you aren't telling us about? what does between "null" and "AB20" mean unless it is ordered? – PressingOnAlways Feb 21 '14 at 07:10
  • one can enter a from id and to to_id.if any one is missed i need that the query should run.Not sure the ID will be sorted or not – user956021 Feb 21 '14 at 07:12
  • where are `from_id` and `to_id` represented in the query? – Mureinik Feb 21 '14 at 07:14
  • All you have to do is to use NVL(): WHERE id BETWEEN NVL(to_char(:id_column,'AB20') AND 'AB20'. I assumed your id is number. Otherwise no need to convert to to_char(). – Art Feb 21 '14 at 14:17

2 Answers2

4

One way is to just stop using BETWEEN; it's just the contraction of two Boolean's anyway, split them out.

select *
  from student
 where ( id >= :start_id
         or :start_id is null
          )
   and ( id <= :end_id
         or :end_id is null
          )
  • If start_id is null then you'll get everything less than end_id, i.e. everything from the "start" of your sequence to end_id.
  • If end_id is null then you'll get everything greater than start_id.
  • If both are null then you'll get everything.

We're all assuming here, based on your question that the column ID has some intrinsic meaning; if it doesn't then this won't work.

Ben
  • 51,770
  • 36
  • 127
  • 149
1

You have to provide some value for between clause, null is no value.

You can change you code to cope up with the situation, something like this can help

select * 
  from student 
 where id between nvl(:from_value, (select min(id) from student)) 
          and nvl(:to_value, (select max(id) from student));

as per Ben's suggestion, better to use COALEASE

select * 
  from student 
 where id between COALESCE(:from_value, (select min(id) from student)) 
          and COALESCE(:to_value, (select max(id) from student));

COALESCE returns the first not null value from the list.

San
  • 4,508
  • 1
  • 13
  • 19
  • You can use `DECODE()` instead of `NVL()`, since `MIN()` will be calculated internally all the time, even if the other value is `NOT NULL`. (If it is expected to be a performing _serious_ SQL.) – Maheswaran Ravisankar Feb 21 '14 at 09:17
  • @OracleUser As far as I know, the sub queries are not co-related, they will be executed once only and the result will be cached in Oracle to be used further. Correct me if I am wrong. Anyways, as per OP requirement, the whole query will be fired just once. – San Feb 21 '14 at 09:27
  • Actually, you should use COALESCE() instead of NVL() @OracleUser. NVL() does not short-circuit but COALESCE() does. Equally, it's best to [avoid DECODE() in favour of CASE](http://stackoverflow.com/questions/13712763/are-a-case-statement-and-a-decode-equivalent) if possible. San, your query will use [scalar sub-query caching](http://www.oracle.com/technetwork/issue-archive/2011/11-sep/o51asktom-453438.html). – Ben Feb 21 '14 at 09:37
  • @Ben that was a nice article. I have been always educated with using `DECODE`.. I shall read further. And San, I was trying to tell `NVL()` process both its operands all the time. So, we have to use something which short circuits(`COALESCE`/`DECODE`), unless we just work on values in `NVL()` – Maheswaran Ravisankar Feb 21 '14 at 10:04