Re: All rows if null

On Apr 6, 12:07�pm, Urs Metzger <u..._at_ursmetzger.de> wrote:
> HansH schrieb:
>
>
>
> > "banaslee" <banas..._at_gmail.com> schreef in bericht
> >news:a0eae9d2-cd8c-4acd-bca0-7a5050285a1f_at_d2g2000pra.googlegroups.com...
> >> 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