Query problem (probably easy)

[Guest_imported]

This is probably obvious to everyone except me...

My telephone directory is a single table. The search query is as follows:

select LAST_NAME, TEL_NUM, DEPT, SECTION
from ALL_R
where ((upper(LAST_NAME) LIKE upperLast_Name)||'%')
AND ((upper(DEPT) like '%'||upperkeyword)||'%')
OR (upper(SECTION) like '%'||upperkeyword)||'%')
AND(TEL_NUM LIKE '%'||Tel_Num)||'%'))
order by LAST_NAME ASC

This works fine for seaches on name, keyword or number in that it returns all the people required. What my query doesn't do is return rows which have the name missing e.g. dept fax numbers. However I change it I have problems - HELP PLEASE.

Badger, UK

 

[JHoarty]

Whereas you have lots of parens, they aren't also used to organize your query. Point is this,

SELECT a AND b OR c

can mean that you want [a AND b] OR c
or it can mean you want a AND [b or c]

Try using parenthesis to make it more explicit, as:

SELECT (a AND b) OR c
FROM table
WHERE a LIKE '%keyword%' John Hoarty
jhoarty@quickestore.com

http://www.quickestore.com

 

[karluk]

I think you will have to add a test for nulls on each column that may have nulls. Try something like

select LAST_NAME, TEL_NUM, DEPT, SECTION
from ALL_R
where ((upper(LAST_NAME) LIKE upperLast_Name)||'%'
or last_name is null)
AND ((upper(DEPT) like '%'||upperkeyword)||'%'
or dept is null)
OR (upper(SECTION) like '%'||upperkeyword)||'%'
or section is null)
AND(TEL_NUM LIKE '%'||Tel_Num)||'%')
or tel_num is null)
order by LAST_NAME ASC

 

[carp]

Along the same lines as Karluk:

select LAST_NAME, TEL_NUM, DEPT, SECTION
from ALL_R
where ((upper(NVL(LAST_NAME,:Last_Name)) LIKE upperLast_Name)||'%')
AND ((upper(NVL(DEPT,:keyword)) like '%'||upperkeyword)||'%')
OR (upper(NVL(SECTION,:keyword)) like '%'||upperkeyword)||'%')
AND(NVL(TEL_NUM,:Tel_Num) LIKE '%'||Tel_Num)||'%'))
order by LAST_NAME ASC