db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: how to suppress similar rows while copying from one table to another?
Date Mon, 19 Nov 2007 13:35:20 GMT
Dag H. Wanvik wrote:
> Daniel John Debrunner <djd@apache.org> 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


Mime
View raw message