Ignoring fields in SQL Where Statement
Home > IT Answers > Development > Ignoring fields in SQL Where Statement
Charrdzc
5 pts.
0
Q:
Ignoring fields in SQL Where Statement
SQL, SQL/400, WHERE statement, SQL WHERE statement
Can fields in a where statment be ingnored. Such as Where name = :name and date = :date, but if I just pass in a name I'd like the where to select only on the name and ignore the date.
ASKED: Oct 31 2008  8:13 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Carlosdl
29855 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
In oracle exists a function NVL(a,b) which will return b if a is null.
In sql server I think you can achieve the same by using COALESCE or isNull functions.

I don't know if you can use some of these functions in AS/400. I think COALESCE is based on the ANSI SQL standard, so it is possible that you can use it.

If that's the case, the query should be constructed like this:

select *
 from your_table
    where name = :name
    and date = coalesce(:date,date)


This way, if :date is null, the date field will be compared to itself, which produces the same result as if the condition did not exist.

Hope this helps.
Last Answered: Oct 31 2008  8:48 PM GMT by Carlosdl   29855 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Gilly400   23625 pts.  |   Nov 3 2008  12:58PM GMT

Hi,

I normally do something along these lines :-

Select xxxxx
From xxxxx
Where (name = :name or :name = ”) and (date = :date or :date = 0)

May not be the nicest way of doing this, but it works.

Regards,

Martin Gilbert.

 
0