Re: All rows if null
Date: Sun, 6 Apr 2008 16:26:55 -0700 (PDT)
On Apr 6, 12:07 pm, Urs Metzger <u..._at_ursmetzger.de> wrote:
> HansH schrieb:
> > "banaslee" <banas..._at_gmail.com> schreef in bericht
> >> Why am I building the query dynamically? I have a search tool based
> >> ona form with some text boxes, one for name, other for the date of birth
> >> and another for ID number. Then, based on the values entered I want
> >> to search for persons that match those criteria.
> >> Imagine, if I have 'John' on name and all the other boxes empty then I
> >> only want to see people with his name starting by John no matter his
> >> ID number or his DOB.
> >> This is why I use dynamic SQL:
> >> 'select * from people where status = ''A''' || where_clauses
> >> Where where_clauses can be 'AND name LIKE ''John%''' or 'AND id_number
> >> = 12345' or 'AND name LIKE ''John%'' AND id_number = 12345' or even
> >> more possible arrangements (with DOB too of course).
> >> I don't know another way of doing this :/
> > SELECT * FROM people WHERE status = 'A'
> > AND ( '' = :name OR name LIKE :name||'%' )
> > AND ( '' = :dob OR name = :dob )
> > Should have given this more thought before posting ... just 2 cnt
> > HansH
> "'' = :name" won't work, as '' is NULL;
> "AND (:name is null or name like :name || '%')
> AND (:dob is null or dob = :dob )"
> will do the trick.
> Hth, Urs Metzger
Thanks for your answer.
I considered that but dropped that idea because of performance issues. Let me know what you think ;)
Thanks for your answers Received on Sun Apr 06 2008 - 18:26:55 CDT