On Apr 6, 12:07=A0pm, Urs Metzger <u...@[EMAIL PROTECTED]
> wrote:
> HansH schrieb:
>
>
>
> > "banaslee" <banas...@[EMAIL PROTECTED]
> schreef in bericht
>
>news:a0eae9d2-cd8c-4acd-bca0-7a5050285a1f@[EMAIL PROTECTED]
> >> 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
birt=
h
> >> 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 =3D ''A''' || where_clauses
> >> Where where_clauses can be 'AND name LIKE ''John%''' or 'AND
id_number
> >> =3D 12345' or 'AND name LIKE ''John%'' AND id_number =3D 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 =3D 'A'
> > AND ( '' =3D :name OR name LIKE :name||'%' )
> > AND ( '' =3D :dob =A0OR name =3D =A0 =A0:dob )
>
> > Should have given this more thought before posting ... just 2 cnt
>
> > HansH
>
> "'' =3D :name" won't work, as '' is NULL;
>
> "AND (:name is null or name like :name || '%')
> =A0 AND (:dob is null or dob =3D :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
|
