Re: How do you perform SOUNDEX queries in Forms Query Mode?

In article <9512122143.AA22785_at_enzomc>, emaini_at_sikorsky.com (Enzo Maini) says:
>
>Does anyone have any experience in using SOUNDEX with Forms 4.0. Users would
>like to enter the following for the query:
>
><Customer Name> : SOUNDEX('Jones')
>
>The proper use of SOUNDEX within Oracle is
>
>SELECT first_name, last_name, company_name
> FROM emp
> WHERE SOUNDEX(last_name) = SOUNDEX('Jones');
>
>This is a cool way to find misspellings.
>
>If you put the following in a field:
>
>#SOUNDEX('Jones')
>
>You get an error, because Oracle Forms writes the SQL as such:
>
>SELECT first_name, last_name, company_name
> FROM emp
> WHERE last_name SOUNDEX('Jones');
>
>You could use a field variable like :LN
>but then the user would have to type
>
>SOUNDEX('LAST_NAME') = SOUNDEX('Jones')
>
>Which seems unreasonable because now each user would need to know the
>attributes of the table query on fields.
>
>
>Does anybody have any good ideas?
>
>(Seems like Oracle should have scanned for this operator like it does for the
>'Like' operator and performed the proper query--Oh well!)
>

Try a PRE-QUERY trigger on the block. If you have a LASTNAME form field along with a table column LNAME (I named them different so you can easily differiate between the two below), create a block level PRE-QUERY trigger:

Begin
  :BLOCK.LASTNAME := '# = LNAME and SOUNDEX('''||

                     :BLOCK.LASTNAME||''') = SOUNDEX(LNAME)';