 | |
#11
 
| |||
| |||
|
Re: All rows if null -
04-05-2008
, 09:26 AM
|
On Apr 4, 4:51 am, banaslee <banas... (AT) gmail (DOT) com> wrote: Hi there. I'm new to oracle and I'd want to build a form search. I'm currently using dynamic sql to append all the where clauses that has no null values on the corresponding form text boxes but I'm searching for a more elegant and static solution. In text I can use WHERE nvl(a.foo, ' ') = b.foo || '%' so that if b.foo is null it returns all the a.foo rows I'd be interested in seeing this work because I do not believe it. if b.foo is NULL the the Right hand side of the expression becomes just '%' *so the expression becomes essentially a.foo='%' which does NOT return all the a.foo rows Perhaps you meant WHERE nvl(a.foo, '%') = b.foo || '%' ? *but that only returns rows where both a.foo and b.foo are null, so you must have meant WHERE nvl(a.foo, ' ') *LIKE b.foo || '%' *... * * and I can still use an index if I create it like CREATE INDEX foo_idx ON a(nvl(foo, ' ')). But what about number values? Is there any better solution for them? use to_char() and your previous condition works. I hope I had been enough specific on my question. Well it is not really clear to me even though I gave some remarks. You are building the query dynamically because...? Thanks in advance for your help, Fábio Oliveira HTH, * Ed |
Quote:
|
| '%'. Sorry about that :P |
can't be as fast as numeric comparision even if I can build a function
index.
Why am I building the query dynamically? I have a search tool based on
a 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 :/
#12
| |||
| |||
|
|
On Apr 4, 4:51 am, banaslee <banas... (AT) gmail (DOT) com> wrote: Hi there. I'm new to oracle and I'd want to build a form search. I'm currently using dynamic sql to append all the where clauses that has no null values on the corresponding form text boxes but I'm searching for a more elegant and static solution. In text I can use WHERE nvl(a.foo, ' ') = b.foo || '%' so that if b.foo is null it returns all the a.foo rows I'd be interested in seeing this work because I do not believe it. if b.foo is NULL the the Right hand side of the expression becomes just '%' *so the expression becomes essentially a.foo='%' which does NOT return all the a.foo rows Perhaps you meant WHERE nvl(a.foo, '%') = b.foo || '%' ? *but that only returns rows where both a.foo and b.foo are null, so you must have meant WHERE nvl(a.foo, ' ') *LIKE b.foo || '%' *... * * and I can still use an index if I create it like CREATE INDEX foo_idx ON a(nvl(foo, ' ')). But what about number values? Is there any better solution for them? use to_char() and your previous condition works. I hope I had been enough specific on my question. Well it is not really clear to me even though I gave some remarks. You are building the query dynamically because...? Thanks in advance for your help, Fábio Oliveira HTH, * Ed |
Quote:
|
| '%'. Sorry about that :P |
can't be as fast as numeric comparision even if I can build a function
index.
Why am I building the query dynamically? I have a search tool based on
a 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 :/
#13
| |||
| |||
|
|
On Apr 4, 4:51 am, banaslee <banas... (AT) gmail (DOT) com> wrote: Hi there. I'm new to oracle and I'd want to build a form search. I'm currently using dynamic sql to append all the where clauses that has no null values on the corresponding form text boxes but I'm searching for a more elegant and static solution. In text I can use WHERE nvl(a.foo, ' ') = b.foo || '%' so that if b.foo is null it returns all the a.foo rows I'd be interested in seeing this work because I do not believe it. if b.foo is NULL the the Right hand side of the expression becomes just '%' *so the expression becomes essentially a.foo='%' which does NOT return all the a.foo rows Perhaps you meant WHERE nvl(a.foo, '%') = b.foo || '%' ? *but that only returns rows where both a.foo and b.foo are null, so you must have meant WHERE nvl(a.foo, ' ') *LIKE b.foo || '%' *... * * and I can still use an index if I create it like CREATE INDEX foo_idx ON a(nvl(foo, ' ')). But what about number values? Is there any better solution for them? use to_char() and your previous condition works. I hope I had been enough specific on my question. Well it is not really clear to me even though I gave some remarks. You are building the query dynamically because...? Thanks in advance for your help, Fábio Oliveira HTH, * Ed |
Quote:
|
| '%'. Sorry about that :P |
can't be as fast as numeric comparision even if I can build a function
index.
Why am I building the query dynamically? I have a search tool based on
a 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 :/
#14
| |||
| |||
|
|
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 :/ |
AND ( '' = :name OR name LIKE :name||'%' )
AND ( '' = :dob OR name = :dob )
Should have given this more thought before posting ... just 2 cnt
HansH
#15
| |||
| |||
|
|
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 :/ |
AND ( '' = :name OR name LIKE :name||'%' )
AND ( '' = :dob OR name = :dob )
Should have given this more thought before posting ... just 2 cnt
HansH
#16
| |||
| |||
|
|
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 :/ |
AND ( '' = :name OR name LIKE :name||'%' )
AND ( '' = :dob OR name = :dob )
Should have given this more thought before posting ... just 2 cnt
HansH
#17
| |||
| |||
|
|
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 :/ |
AND ( '' = :name OR name LIKE :name||'%' )
AND ( '' = :dob OR name = :dob )
Should have given this more thought before posting ... just 2 cnt
HansH
#18
| |||
| |||
|
|
"banaslee" <banaslee (AT) gmail (DOT) com> schreef in bericht news:a0eae9d2-cd8c-4acd-bca0-7a5050285a1f (AT) d2g2000pra (DOT) 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 |
"AND (:name is null or name like :name || '%')
AND (:dob is null or dob = :dob )"
will do the trick.
Hth, Urs Metzger
#19
| |||
| |||
|
|
"banaslee" <banaslee (AT) gmail (DOT) com> schreef in bericht news:a0eae9d2-cd8c-4acd-bca0-7a5050285a1f (AT) d2g2000pra (DOT) 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 |
"AND (:name is null or name like :name || '%')
AND (:dob is null or dob = :dob )"
will do the trick.
Hth, Urs Metzger
#20
| |||
| |||
|
|
"banaslee" <banaslee (AT) gmail (DOT) com> schreef in bericht news:a0eae9d2-cd8c-4acd-bca0-7a5050285a1f (AT) d2g2000pra (DOT) 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 |
"AND (:name is null or name like :name || '%')
AND (:dob is null or dob = :dob )"
will do the trick.
Hth, Urs Metzger
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.