CODEWITHSUNDEEP
sqloracle-database
Miracle
Miracle

Reputation: 387

Oracle SQL Where clause

I have a query:

select * from students stud
where stud.grade <= :grd

For example, I entered 80 in :grd, this displays all students that has a grade of 80 and below.

What I wanted is to display all the students if I entered nothing in :grd.

Is there a way to do this? Thanks :)

Upvotes: 0

Views: 43

Answers (4)

thewindlove123
thewindlove123

Reputation: 17

You can try this:

select * from students 
where (
        CASE 
        WHEN :grd  IS NULL THEN 1
        WHEN clct.contractid = :grd THEN 1
        ELSE 0 END = 1
        )

Upvotes: 0

Abra
Abra

Reputation: 20914

Assuming column stud.grade cannot be more than 100 and given that you are using Oracle...

select *
from   students
where  stud.grade <= nvl(:grd, 100)

Upvotes: 1

Popeye
Popeye

Reputation: 35900

You can simply use coalesce as below

select * from students 
where stud.grade <= coalesce(:grd,stud.grade);

Or use case when as below:

select * from students 
where stud.grade <= 
case when :grd is null 
     then stud.grade 
     else :grd end;

Cheers!!

Upvotes: 1

Ed Bangga
Ed Bangga

Reputation: 13006

this query is enough to get what you want.

select * from students where stud.grade < coalesce(:grd, stud.grade) or stud.grade = coalesce(:grd, stud.grade)

Upvotes: 0

Related Questions