I'm working on my deduplication subsystem. Most of the activity consists of lines like

 EXECUTE IMMEDIATE 'UPDATE Calls SET _fkContacts = ' + CAST(NewID AS VARCHAR) + 'WHERE _fkContacts = '+CAST(ZapID AS VARCHAR);

ie just resetting a foreign key in the linked tables.

However, I also want to merge some data in the rows and I'm trying to figure out if I'm better off writing an external function in Delphi or trying to accomplish the task in SQL/PSM.

Most columns are simple - eg address line 1 - if its got something in NewID then leave it, if not copy from ZapID. Some though are CLOBS and some of those have lists eg linked files/documents on disk. In Delphi its easy - couple of stringlistss, sorted and duplicates set to dupignore, load, combine and return to table - job done.

I have no idea how to start doing that in SQL/PSM.

Is it worth it? Hints, suggestions etc appreciated.

Roy Lambert

Roy,

<< Most columns are simple - eg address line 1 - if its got something in
NewID then leave it, if not copy from ZapID. Some though are CLOBS and some
of those have lists eg linked files/documents on disk. In Delphi its easy -
couple of stringlistss, sorted and duplicates set to dupignore, load,
combine and return to table - job done.

I have no idea how to start doing that in SQL/PSM. >>

You'll want to use external functions for anything that involves
manipulating large strings like that.  The good thing, however, is you can
just put the normal Delphi code that you would use into the external module,
call it from the SQL/PSM, and then use the resultant value accordingly.

The rule of thumb with these types of issues is:

1) If it manipulates data in a general way (working with rows and columns),
keep it in SQL/PSM
2) If it manipulates data at the variable level, and is complex, then move
it into an external function and call it from the SQL/PSM

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>You'll want to use external functions for anything that involves
>manipulating large strings like that. The good thing, however, is you can
>just put the normal Delphi code that you would use into the external module,
>call it from the SQL/PSM, and then use the resultant value accordingly.
>
>The rule of thumb with these types of issues is:
>
>1) If it manipulates data in a general way (working with rows and columns),
>keep it in SQL/PSM
>2) If it manipulates data at the variable level, and is complex, then move
>it into an external function and call it from the SQL/PSM

Pretty much the decision I've reached after skimming the manual to see if there's anything I can use.

Roy Lambert