Re: Help! Tricky(?) query in Forms 3.0.

In article <9TcRsc1w165w_at_vicuna.ocunix.on.ca>, frampton_at_vicuna.ocunix.on.ca wrote:

> Hello:
>
> I am having some difficulty with a Forms 3.0 query trigger. I have a
> table which contains employee information, including the employees'
> surnames. The surname is in a "pleasing format", ie. 'Frampton' or
> 'McDonald' as opposed to 'FRAMPTON' or 'MCDONALD'. Hey, I didn't design
> the table! :-)
>
> Anyways, my form allows the user to perform a query based on surname.
> Trouble is, unless they enter the name in the same case it is represented
> in the table, no records are retrieved.
>
> How do I change my query execution to match the upper(form_var) with the
> upper(table_record)? I have tried to play with KEY-ENTQRY as well as
> KEY-EXEQRY but I haven't been able to get this to work.
>
> Any information would be greatly appreciated. Please reply via e-mail; I
> read every posting that comes across in this newsgroup, but my feed
> is...well...less than reliable. :-(
>
> Thanks in advance!
>
> ------------------------------------------------------------
> Steve Frampton E-mail: <frampton_at_vicuna.ocunix.on.ca>

rough outline of solution.

in a pre-query trigger, if the name field is not null, copy it into a non-database field and clear it. let's call the non-database field SNAME.

in the WHERE clause for the block,

  where :sname is null or

        ( ( surname like initcap(substr(:sname,1,2))||'%' or
            surname like upper(substr(:sname,1,2))||'%' or
            surname like lower(substr(:sname,1,1))||'%') and
          upper(vendor_name) like upper(:sname) )

i haven't tested the above, so proceed with caution. we have done things very similar to this. our tests seem to show that the WHERE clause above performs faster than most obvious alternatives - though it's not obvious why.

good luck. feel free to send me email if the above is too confusing. Received on Tue Sep 20 1994 - 18:53:29 CEST