Re: simple query help

On 6 Jan 2000 15:29:02 GMT,
"fumi" <fumi_at_tpts5.seed.net.tw>
wrote in article <852cbu$j9k$2_at_news.seed.net.tw>:

> No.
> You can not predicate which side is "larger".
> And more, you can not assume A is a superset of B, nor B is a superset of A.
>
> Take look at a quick example:
>
> dept dude status
> ==== ==== ======
> north, bob, i,
> east, mary, a,

  I have a backdoor tactic I use a lot. The old von Neumann approach of doing one part at a time. I union the results of multiple subqueries.

SELECT DEPT, MAX(ACT), MAX(INACT) FROM
(
  SELECT DEPT, COUNT(*) AS ACT, 0 AS INACT     FROM DEPT_DUDE
    WHERE STATUS = 'a'
UNION
  SELECT DEPT, 0 AS ACT, COUNT(*) AS INACT     FROM DEPT_DUDE
    WHERE STATUS = 'i'
)
GROUP BY DEPT   This will properly report any DEPT which has *ANY* dudes. If you want to get bulletproof, and report *ALL* departments regardless, we need to have a table DEPT_LIST which lists all departments...

SELECT DEPT, MAX(ACT), MAX(INACT) FROM
(
  SELECT DEPT, COUNT(*) AS ACT, 0 AS INACT     FROM DEPT_DUDE
    WHERE STATUS = 'a'
UNION
  SELECT DEPT, 0 AS ACT, COUNT(*) AS INACT     FROM DEPT_DUDE
    WHERE STATUS = 'i'
UNION
  SELECT DEPT_NAME AS DEPT, 0 AS ACT, 0 AS INACT     FROM DEPT_LIST
)
GROUP BY DEPT   This will report numbers (even if they're both zero) for *ALL* departments. This model could be expanded to handle multiple different statuses. We've got a table at work where the data modeler went berzerk with normalization, and I've had to resort to this stunt to give 10 or 11 columns.   The code is repetitive. To denormalize into N columns, you need N subqueries UNION'd. In each one, N-1 columns are dummies, and one column in each subquery is actually calculated. Here, I've gone one query further to give a bulletproof count that covers the situation of no members in a department.
  Since this is a UNION, rather than a JOIN, the number and type of columns must match in each subquery. The column names must also match, but that can be handled by aliasing with the "AS" subclause as shown at the end of the last query. There is no requirement that all the columns come from the same table.

--
Walter Dnes <waltdnes_at_waltdnes.org>
http://www.waltdnes.org <SpamDunk Project procmail spamfilters> Received on Sun Jan 09 2000 - 15:01:41 CST