Re: Informix SQL ?
>> We are trying to do some wildcarding using a MATCHES statement, however
>> we are running into a problem.
>> I realize that changing the query would allow me to do the above. But, I
>> wanted to find out if there was some method using the above query with the
>> MATCHES clause and a set of wildcarding expressions. Principally because
>> the data is being acquired thru an ACE INPUT statement. My experience tells
>> me there is no way of doing this, but I wanted to make sure.
Yep, the INPUT statement's a prob. One way out of this is to front-end the
ACE report with a parameter gatherer (script or whatever) and then pass them
to the ACE report as parameters after, optionally, prefixing/suffixng the values
with "%" sign - to satisfy the like clause.
Then you decide what the maximum number of these "or" conditions and write
the SQL statement to accept them all:
SELECT whatever_cols
FROM whatever
WHERE col1 LIKE $1
OR col1 LIKE $2
:
OR col1 LIKE $n
Of course, parameters that are not input will have to be defaulted to
impossible values. I've used this approach more commonly with AND filters
while defaulting the un-entered parameters to "%". The LIKE keyword is used
to avoid the "*" wildcards which could be expanded by a parameter/gatherer and I
believe it's a better SQL standard.
Note: Front-ending ACE is just one step toward running them in background
and getting potentially a more user-friendly report subsystem. I
can tell you that I've never written an ACE report that used the
INPUT statement (and I wrote over 400 for one client). Well ...
maybe one - the first one.
Another advantage of this approach is that a variable number of
filter values can be selected by the gatherer - up to a pre-determined
limit.
+-------------------------------+----------------------------------+
| Tim Cargile | INTERNET: tec@netcom4.netcom.com |
| Relational Applications Group | Bus. : 510-743-8211 |
| POB 31171 Oakland, CA 94604 | FAX : 510-743-0971 |
+-------------------------------+----------------------------------+
What this country needs is a good 5 cent Informix Survival Kit