Dag H. Wanvik wrote:
Daniel John Debrunner <[EMAIL PROTECTED]> writes:
Rick Hillegas wrote:
One way to solve this problem would be to put a filtering function
in the WHERE clause of the driving SELECT statement. Something like
this:
INSERT INTO targetTable
SELECT * FROM sourceTable
WHERE isFirstInstance( tastyColumn1, tastyColumn2 ) = 1
Here isFirstInstance is a function which returns 1 the first time it
sees a given key combination and returns 0 on all subsequent
sightings. You, of course, have to write this user function.
Any thoughts on how to do that? Since such calls are stateless how
would you identify it's the first call for a query execution?
Right. I think you could add a context handle as a third argument to the
function. Then you could request a context handle before running the
query and release the context after the query. So now the invocation
looks like this:
VALUES ( getContextHandle() );
INSERT INTO targetTable
SELECT * FROM sourceTable
WHERE isFirstInstance( tastyColumn1, tastyColumn2, contextHandle ) = 1;
CALL releaseContext( contextHandle );
Another solution would be to wrap the SELECT in a filtering table
function. Something like this:
INSERT INTO targetTable
SELECT s.* FROM TABLE( filteringFunction( ) ) s
Regards,
-Rick
