Re: SQL help

From: Robert Stearns <is_at_uga.edu>
Date: Thu, 07 Oct 2004 10:22:03 -0400
Message-ID: <ck3je9$jom$1_at_cronkite.cc.uga.edu>


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@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 - 16:22:03 CEST

Original text of this message