Re: SQL help

From: Robert Stearns <rstearns1241_at_charter.net>
Date: Thu, 07 Oct 2004 21:13:11 -0400
Message-ID: <10mbqd7k1sjcb89@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 Thu Oct 07 2004 - 20:13:11 CDT