Re: All rows if null

"banaslee" <banaslee@[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 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