HansH schrieb:
> "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
>
"'' = :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
|
