Re: All rows if null
From: Urs Metzger <urs_at_ursmetzger.de>
Date: Sun, 06 Apr 2008 13:07:34 +0200
Message-ID: <47F8AEF6.5000702@ursmetzger.de>
>> 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 :/
Date: Sun, 06 Apr 2008 13:07:34 +0200
Message-ID: <47F8AEF6.5000702@ursmetzger.de>
HansH schrieb:
> "banaslee" <banaslee_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 Received on Sun Apr 06 2008 - 06:07:34 CDT