Re: Wildcard and 4gl code

On Thu, 20 Jul 2000, Richard E. Hassler wrote: >Jonathan, >Below is a posting I made and beneath that the one answer I received. >I don't understand the one answer so don't know if it is right or >not. Could you possibly take a look at the problem and let me know >what you think. >Sorry to impose. If you need more of the code I will be happy to oblige. >Thanks, >Dick > >From: "Richard E. Hassler" <Hassler.1@osu.edu> >Subject: Wildcard and 4gl code >Date: Wed, 21 Jun 2000 10:02:46 -0400 >Newsgroups: comp.databases.informix >Message-ID: <Hassler.1-7950C0.10024621062000@nntp.service.ohio-state.edu> > > >I'm running Informix SE 7.2 on an HP9000 390R running HP-UX 10.20. The >4gl code below was written when I was using Informix On-Line 5.0 on an >HP9000, 832 running HP-UX 9.0. This code worked on the old system but >not on the new, i.e. the asterisk would serve as a wildcard on the old >system whereas now it seems to function as an asterisk and none of the >names are asterisk. Can someone tell me what I have to do to get it to >work on the new system? > > if (tdata[i].last is null) then > let tdata[i].last = "*" > end if > if (tdata[i].first is null) then > let tdata[i].first = "*" > end if > > select count(*) into total from faculty where > (tdata[i].last matches f_last) and > (tdata[i].first matches f_first) So you're trying to execute WHERE "*" MATCHES f_last? MATCHES was never intended to be a commutative operator, and it was a bug that it ever worked the 'wrong way round'. > if (total = 1) then > select rowid, f_last, f_first > into rownumber[i], tdata[i].last, tdata[i].first > from faculty > where (tdata[i].last matches f_last) and > (tdata[i].first matches f_first) > end if > if (total = 0) then > call warning(i) > end if> >Also, will there ever be an SE 7.3 since I see a lot of changes between >7.2 coding of select statements and 7.3 coding of select statements >according to this newsgroup (see sample below). > >SELECT link_id, > CASE > WHEN (CURRENT - date) < INTERVAL (1) DAY TO DAY > THEN "1" > WHEN (CURRENT - date) < INTERVAL (7) DAY TO DAY > THEN "2-7" > ELSE "8-30" > END, > COUNT(*) >FROM jump >WHERE (CURRENT - date) < INTERVAL (30) DAY TO DAY >GROUP BY link_id, 2;> >Thanks, >Dick >---------------------------------------------------------------------- >ONE ANSWER RECEIVED: > >Depending on where this falls within your code and what you're trying >to accomplish, there are a couple of ways I would handle this. > >One is to build a select_statement from a where_clause char(300) >based on your if statements. > >if (tdata[i].last is not null) then > let where_clause = " where ", > tdata[i].last, > " matches f_last" >end if > >if (tdata[i].first is not null) then > if (tdata[i].last is not null) then > let where_clause = where_clause clipped, > " and ", > tdata[i].first, > " matches f_first" > else > let where_clause = " where ", > tdata[i].first, > " matches f_first" > > end if >end if > >select_statement = "select whatever from wherever", > where_clause clipped > > >The other way would be to use the same logic and copy your selects >(with variations) into nested if statements. > >If this seems fuzzy, blame it on typing one-handed while under the >influence of pain medication. > >carlos > >== >Maintainer of the procrastinator's FAQ. >Well, maybe tomorrow I will be. > >_____________________________________________________________ >Want a new web-based email account ? ---> http://www.firstlinux.net > -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"