Re: SQL help
Date: Thu, 07 Oct 2004 21:13:11 -0400
Message-ID: <10mbqd7k1sjcb89_at_corp.supernews.com>
--CELKO-- wrote:
>>>I have not passed Cello's year to get used to thinking in sets, so
>
> I do
> not have the skill to write an appropriate subquery for the following
> specification .. <<
>
> At least you get time for free. Just sit and it comes to you.
>
> Looking at this, I am confused. There is an Owners table, but I do
> not see an owner identifier in it. I would assume that onwers are
> entities and not relationships. What exactly do you want to get back
> from the query?
>
> Also "entity_id" is a terrible name; can there be an identifier that
> references a non-entity? It is too vague -- identifier of exactly
> what? I also did not recognize the dialect of SQL you were using.
> Considering how many of them I have worked on, that is saying
> something. Minor nit: DEFAULT comes before NOT NULL in Standard SQL.
> CREATE TABLE owners
(
bhid BIGINT NOT NULL, -- Thing owned entity_id INTEGER NOT NULL, -- Owner; it may be a person, -- A farm, a consortium, a -- Corporation, etc. Entity -- was the noun I came up -- with; entities have -- other roles than -- ownership: employer, -- seller, buyer, etc starting DATE NOT NULL, -- when a particular period -- period of ownership -- started percent DECIMAL(7, 4) NOT NULL DEFAULT 100, -- fraction owned ending DATE -- when a particular period -- ended; NULL means still -- owned)
DATA CAPTURE NONE;
>
>>>The longest owner: MIN(starting) AND starting<=gd AND (ending IS
>
> NULL OR ending>=gd) <<
>
> The pseudo-code is a bit hard to understand; is this what you meant?
>
> SELECT bh_id, entity_id, starting, percent, ending
> FROM Owners
> WHERE :gd BETWEEN (SELECT MIN(starting) FROM Owners)
> AND COALESCE (endding, CURRENT_TIMESTAMP)
I meant
SELECT * FROM IS3.Owners WHERE Bhid IN <some query set> AND Starting=(
SELECT MIN(Starting) FROM IS3.Owners WHERE Starting<=:gd AND Bhid = <the owned object currently being tried> AND (Ending IS NULL OR Ending>=:gd) );
Your solution might work equally well, I do not always think of COALESCE. I also do not know how to achieve having the interior subselect use the "current" Bhid. As I said this part of a much larger query.
>
>
>>>If there ties, MAX(percent) among them <<
>
>
> Nest the first query inside another one ..
>
> SELECT X.bh_id, X.entity_id, X.starting, X.percent, X.ending
> FROM (<>)AS X(bh_id, entity_id, starting, percent, ending)
> WHERE X.percent
> = (SELECT MAX(percent)
> FROM (<>));
>
> This is where you really want a WITH operator
>
>
>>>If there are still ties, entity_id = :ge_id if it exists else
>
> longest membership: MIN(entity_id), The last is guaranteed to be
> unique. <<
>
> I am reading that to mean:
>
> SELECT bh_id, entity_id, starting, percent, ending
> FROM <??> -- horrible nested thing
> WHERE entity_id = COALESCE (:ge_id, (SELECT MIN(entity_id) FROM
> <??>))
>
> Sounds like more nesting, but if entity_id has to do with memberships,
> why isn't it named "membership_nbr" or something that explains itself?
You took :ge_id (given entity id as :gd stands for given date) to be NULL or not, a valid interpretation of my ambiguous specification. What I meant was if :ge_id is the set of remaining candidates, it is the winner; otherwise owner with the minimum entity_id among the remaining candidates is.
Entities can also be members of various organizations, but that is handled in some tables called organizations and organization_members, which is an entanglement we need not address here.
My db is DB2 UDB V8.1.6 under LINUX; all the DML/DDL in the original post was taken from working scripts.
The WITH operator was pointed out to me by another poster, and I will try to formulate this subquery using it. What performance impact will this subquery have on the query over all? Received on Fri Oct 08 2004 - 03:13:11 CEST