All rows if null

#21

HansH schrieb:

Quote:

"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

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

#22

On Apr 6, 12:07*pm, Urs Metzger <u... (AT) ursmetzger (DOT) de> wrote:

Quote:

HansH schrieb:

"banaslee" <banas... (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

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

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

#23

On Apr 6, 12:07*pm, Urs Metzger <u... (AT) ursmetzger (DOT) de> wrote:

Quote:

HansH schrieb:

"banaslee" <banas... (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

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

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

#24

On Apr 6, 12:07*pm, Urs Metzger <u... (AT) ursmetzger (DOT) de> wrote:

Quote:

HansH schrieb:

"banaslee" <banas... (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

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

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

#25

On Apr 6, 12:07*pm, Urs Metzger <u... (AT) ursmetzger (DOT) de> wrote:

Quote:

HansH schrieb:

"banaslee" <banas... (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

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

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

#26

On 4 Apr., 12:51, banaslee <banas... (AT) gmail (DOT) com> wrote:

Quote:

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 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?

I hope I had been enough specific on my question.

Thanks in advance for your help,
Fábio Oliveira

For different possibilities look here
http://asktom.oracle.com/pls/asktom/...:1288401763279

Gints Plivna
http://www.gplivna.eu

#27

On 4 Apr., 12:51, banaslee <banas... (AT) gmail (DOT) com> wrote:

Quote:

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 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?

I hope I had been enough specific on my question.

Thanks in advance for your help,
Fábio Oliveira

For different possibilities look here
http://asktom.oracle.com/pls/asktom/...:1288401763279

Gints Plivna
http://www.gplivna.eu

#28

On 4 Apr., 12:51, banaslee <banas... (AT) gmail (DOT) com> wrote:

Quote:

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 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?

I hope I had been enough specific on my question.

Thanks in advance for your help,
Fábio Oliveira

For different possibilities look here
http://asktom.oracle.com/pls/asktom/...:1288401763279

Gints Plivna
http://www.gplivna.eu

#29

On 4 Apr., 12:51, banaslee <banas... (AT) gmail (DOT) com> wrote:

Quote:

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 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?

I hope I had been enough specific on my question.

Thanks in advance for your help,
Fábio Oliveira

For different possibilities look here
http://asktom.oracle.com/pls/asktom/...:1288401763279

Gints Plivna
http://www.gplivna.eu

#30

On Apr 13, 5:08 pm, Gints Plivna <gints.pli... (AT) gmail (DOT) com> wrote:

Quote:

On 4 Apr., 12:51, 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 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?

I hope I had been enough specific on my question.

Thanks in advance for your help,
Fábio Oliveira

For different possibilities look herehttp://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1...

Gints Plivnahttp://www.gplivna.eu

Thanks a lot! I was looking for an explanation like that since the
beginning. "Cursor" is not the best title for that article though.