| select query help [message #2263] |
Tue, 02 July 2002 14:17  |
mrweatherbee
Messages: 13
Registered: June 2002
|
Junior Member |
|
|
Hi guys, I'm having a little trouble with trying to search through an employee table. Basically i have a web form with 4 text boxes (for employeeid, first, last and status) and a submit button. when a user types into these boxes and submits the form, a sql string is generated that will return any records where the field begins with the text the user types into the boxes. for example: if they entered 12, J, Cl and a then the form would return the record
1234 Jim Clemons Active
1236 Joan Cline Active
the string that i've generated is
SELECT * FROM EmpTable WHERE EMPID LIKE '12%' AND LASTNAME LIKE 'J%' AND FIRSTNAME LIKE 'Cl%' AND STATUS LIKE '%' ORDER BY LASTNAME
so far i'm having 2 problems though i was hoping you could help me out with.
1) how do i write the query to not be case sensitve (ex. if i type in CL or cl it will still return Cline)
2) it seems as though if one of the these fields is missing in the record, the query won't return it, even if it's not the field being searched. for example if Joan Cline does not have a status and a user typed in 12, J, Cl only Jim would be returned and not Joan's record, even though she satisfies these criteria? I'm not sure if i'm doing something wrong, so any help would be greatly appreciated...thank you SO much!
|
|
|
|
| Re: select query help [message #2267 is a reply to message #2263] |
Tue, 02 July 2002 16:08  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Not to overload you, but you should really be using bind variables with a dynamic statement like this, but..
1) To make your search case-insensitive, add the UPPER function around your name columns. You will have to uppercase the search criteria (if the user types in 'Cl', you will need to convert that to 'CL').
2) For columns like status where a NULL value is allowed, you can add the NVL function around the column.
So, your statement becomes something like:
select *
from emptable
where empid like '%'
and upper(lastname) like upper('J%')
and upper(firstname) like upper('Cl%')
and nvl(status, 'null') like '%'
order by lastname;
Note that using these functions will not allow the optimizer to use any index (if present), unless you use function-based indexes.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
