Re: Comparing Text fields in Stored Procedures
If you are comparing fields in the same table and in
the same record (row) then you could use something
like this:
SELECT a.field1, b.field2
FROM table1 a, table1 b
WHERE a.textfield1 = b.textfield2
AND a.unique_field = b.unique_field
You can execute this SQL directly or create a stored
procedure:
create procedure compare_text()
define key like table1.unique_field;
foreach SELECT unique_field
INTO key
FROM table1 a, table1 b
WHERE a.textfield1 = b.textfield2
AND a.unique_field = b.unique_field
return key with resume;
end foreach;
end procedure;
You can excute this procedure with this SQL statement:
execute procedure compare_text();
This will return all the rows that have text fields
that are equal. As far as I can tell, in this
example, whether you execute the SQL or create and
execute a stored procedure, all the work is being done
on the server and I don't see any performance issues.
Hope this helps.
Ben
--- tferrante@my-deja.com wrote:
>
>
> I need to compare text fields for equality in a
> stored procedure. As
> far as I know Informix won't let you say WHERE [text
> field] = [text
> field] or use the WHERE [text field] IN (Select
> [text field]...).
>
> Is there any way to do this comparison on the server
> side?
>
> If there is no other way I will have to pull lots of
> data to the client
> (VB with ADO recordsets) and use them for the
> comparison since they
> don't have the comparison constraints or use a UNIX
> perl DBI script.
>
> Any ideas?
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
__________________________________________________
Do You Yahoo!?
Send instant messages with Yahoo! Messenger.
http://im.yahoo.com/