| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: SQL Question
Wade Koteras wrote:
>
> I am using the Microsoft Internet Database Connector to hook some Java
> forms to an Oracle database. I'm running into a problem with a statement
> similar to the following:
>
> select ... where lease_no like '%form_lease_no%'
>
> What happens is that if the user does not enter a search value for the
> form_lease_no variable, the Internet Database Connector fills it in with
> a wildcard (%). The problem is that the wildcard will not match a null
> field (I think) and so records with null lease_no fields do not match.
> Is there a way around this in SQL or will I have to resort to generating
> the SQL from within Java?
In my example, 'your_expression' evaluates to '%form_lease_no%' in your original statement. This statement uses it twice:
select ... where (lease_no like 'your_expression'
and 'your_expression' <> '%%')
or ('%form_lease_no%' = '%%')
This will apply the 'like' clause to the lease_no field only when a value has been entered.
Paul
 |
 |