SQL search on Calculated field with read only database 
Author Message
 SQL search on Calculated field with read only database

I have a Paradox 7.0 table on CD-ROM that is *read only*, and which
contains a SURNAME field. I want my users to be able to run a query that
SELECTs those records where the Soundex value of the SURNAME field is
the same as the one they have entered on a form. I cannot add a Soundex
field when creating the original read only table since I want the users
to be able to vary the Soundex algorithm for length and different
matching rules. When the user runs a query the Soundex value of a
surname is calculated by a function Soundex(AString: string)string;

Ideally I'd like to be able to write something like:

SQL.Add('SELECT * FROM MYTABLE');
SQL.Add('WHERE Soundex(SURNAME) = :Code');
ParamByName('Code').AsString := edCode.Text;
Open;

But of course I can't do this as is. I have two methods that work

1. Create a calculated field for the Soundex value in the TQuery, and
then Filter that. However, this is extremely slow.

2. Iterate the original read only table and create a temporary table at
runtime on the user's hard drive with the runtime Soundex field added.
Now run a Query against that temporary table, then delete it. This
involves a lot of disk thrashing but is faster.

Is there any way that I can effectively create a query in memory with a
calculated field, and then run a second query against it?
--
Barney Tyrwhitt-Drake

Drake Software web site:   http://www.*-*-*.com/



Wed, 18 Jun 1902 08:00:00 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. SQL and MsAccess, Calculated fields...

2. Accessing a Calculated Field from SQL - Novice Question

3. SQL for calculated field value

4. How to use calculated fields from TTable in SQL queries

5. SQL-function SUM on a calculated field?

6. Referencing a calculated field in TQuery SQL

7. How to re-calculate a calculated field

8. calculating logical fields with sql?

9. Calculated fields and TQuery SQL property

10. Q:SQL:String Field fuzzy search

11. Searching for anyword on a database field

12. SQL Wildcard Search of a Blob Field

 

 
Powered by phpBB® Forum Software