Thanks. I hadn't seen the WITH clause before (just how many pages of DB2
UDB documentation are there, any way), but looked it up in the SQL
reference Vol. 1.
Lennart Jonsson wrote:
> Robert Stearns <rstearns1241_at_charter.net> wrote in message news:<10m6rtb9k95it9c_at_corp.supernews.com>...
>
>>This is cross posted because I honestly do not know which of the two
>>news groups is more appropriate or more likely to yield a reply.
>>
>
> [...]
>
>>The longest owner: MIN(starting) AND starting<=gd AND (ending IS NULL OR
>>ending>=gd)
>>If there ties, MAX(percent) among them
>>If there are still ties, entity_id=geid if it exists
>>else longest membership: MIN(entity_id)
>>The last is guaranteed to be unique.
>
>
> [...]
>
>
>>CREATE TABLE owners
>> (
>> bhid BIGINT NOT NULL,
>> entity_id INTEGER NOT NULL,
>> starting DATE NOT NULL,
>> percent DECIMAL(7, 4) NOT NULL DEFAULT 100,
>> ending DATE
>> )
>> DATA CAPTURE NONE;
>>
>>ALTER TABLE owners
>> ADD CONSTRAINT primary PRIMARY KEY
>> (bhid, entity_id, starting);
>
>
> [...]
>
> One way is to so something like:
>
> with suspects_1 (bhid, entity_id, starting, duration) as (
> select bhid, entity_id, starting, (ending - starting) seconds
> from owners
> where starting<=gd AND (ending IS NULL OR ending>=gd)
> ), suspects_2 (bhid, entity_id, starting, percent) as (
> select s1.bhid, s1.entity_id, s1.starting, o.percent
> from suspects_1 s1, owner o
> where (s1.bhid, s1.entity_id, s1.starting) = (o.bhid, o.entity_id, o.starting)
> and starting = (select MIN(starting) from suspects_1)
> ), suspects_3 (bhid, entity_id, starting) as (
> select bhid, entity_id, starting
> from suspects_2
> where percent = (select min(percent) from suspects_2)
> ...
>
>
>
> HTH
> /Lennart
Received on Thu Oct 07 2004 - 09:22:03 CDT
