Path: utzoo!utgpu!news-server.csri.toronto.edu!rpi!usc!elroy.jpl.nasa.gov!decwrl!mejac!orchard.la.locus.com!prodnet.la.locus.com!jfr
From: jfr@locus.com (Jon Rosen)
Newsgroups: comp.databases
Subject: Re: How do I make this class of report?
Message-ID: <24226@oolong.la.locus.com>
Date: 9 May 91 17:02:45 GMT
References: <1991May8.162003.15605@mck-csc.mckinsey.com>
Organization: Locus Computing Corp, Los Angeles
Lines: 114

In article <1991May8.162003.15605@mck-csc.mckinsey.com> hin@mck-csc.mckinsey.com (John K. Hinsdale) writes:
>Hello netland, I have a rather simple question about how to generate a
>particular class of report from an SQL-based database (i.e., Oracle):
>
>The problem is essentially to take a database table and generate
>output whose columns are determined by values of an element in the table.
>(What does *that* mean?)  Here's a simple example:
>
>NAME       YEAR   SALARY
>-----      ----   ------
>Callahan     88    20000
><lines of data deleted...>
>O'Connor     90    34000
>
>I'd like to generate a report that shows the salary history across the page:
>
>NAME            88      89      90
>------      ------  ------  ------
>Callahan     20000   23000   27000
>Muldoon      15300   20000  <null>
>O'Connor    <null>   30000   34000
>
>I.e., the columns in the output DEPEND ON THE DATA in the input - one
>column gets made for each unique value of the YEAR element in the input.
>
>Now, I seem to recall that doing such a thing in the FOCUS DBMS was
>pretty trivial (using its ACROSS verb).  But this appears to be a real
>pain, if not impossible, using Oracle's SQL*Plus.
>

Ah, the simplicity of the ACROSS ordering phrase... (And let's give
credit where credit it due, the ACROSS phrase came from RAMIS and was
adopted as part of the FOCUS language by the author, Gerry Cohen, who
just happened to also be the original author of RAMIS)... 
 
Anyway, you got it right on the nose... In SQL, this is a nightmare
if you only want to use SQL...  The embedded SQL mavens will tell
you that this is a task for a program or report writer, i.e., you go
ahead and execute the SQL using a normal ORDER BY clause and include
year in the output... Then as you FETCH the rows, you hold each salary 
item for the person until you get the last person and then print the
row... 
 
This is of course a headache and more complex reports are harder to
accomplish...  
 
If you want to do this in raw SQL, there are solutions but
each has some limitations...
 
One possibility is a multi-way join, done once for each year:
 
SELECT Y88.EMP, SUM(Y88.SALARY), SUM(Y89.SALARY), SUM(Y90.SALARY)
FROM EMPTABLE Y88, EMPTABLE Y89, EMPTABLE Y90
WHERE Y88.EMP = Y89.EMP 
  AND Y88.EMP = Y90.EMP
  AND Y88.YEAR = 1988
  AND Y89.YEAR = 1989
  AND Y90.YEAR = 1990
GROUP BY Y88.EMP
ORDER BY Y88.EMP

This has several problems... First, it is gross and ugly and takes
a LOT of syntax for something that RAMIS/FOCUS and other 4GLs do
with a simple action word... Second, it only works if you know
the exact years that are desired... It is MUCH harder to make
this work if you want all years in the database and you don't 
know for sure which years are there... Third, if there are ANY
employees that skip some of the years, you need an OUTER JOIN
capabability or else you have to add the complexity of another
set of joins to handle the potential null values...   This is
also very difficult as the number of items you wish to handle
gets large...         
 
If SQL had an IF function (like the IF function in Lotus 1-2-3,
not the IF statement of a programming language like C - hmmm,
actually like the trinary operator of C: a>b ? x : y) then you
could do this:
 
  SELECT EMP, SUM(IF(YEAR=1988,SALARY,0)),
              SUM(IF(YEAR=1989,SALARY,0)),   
              SUM(IF(YEAR=1990,SALARY,0))   
  FROM EMPTABLE WHERE YEAR IN (1988,1989,1990)
  ORDER BY EMP
  GROUP BY EMP
 
This would be reasonable.. But there are NO SQLs that I know of
that currently support a real IF function... The SQL2 standard
draft includes a CASE clause which has the same properties
althought the syntax is more verbose:

  SELECT EMP, SUM(CASE WHERE YEAR=1988 THEN SALARY OTHERWISE 0), etc...

Of course, no one has yet done an SQL2 compatible dbms, because
the standard draft still has not been approved.
 
As a side note, another thing both RAMIS and FOCUS could do is handle
groupings at different levels by stating a domain (not an SQL-type
domain) for each different level of aggregation... Thus, if I wanted
to get the salary for each salesman and the total salary by region
(for a single year, lets say 1988), in RAMIS I could say:
 
  WRITE SALARY BY REGION BY TOTAL SALARY WITHIN REGION BY SALESMAN
 
(Both RAMIS and FOCUS overload the BY clause with aggregation and
ordering responsibilty which has always been a disadvantage, IMHO,
of course, but there is still more power here)...
 
I leave the SQL to do this simple report as an exercise for the
user's vivid imagination :-)
 
Good luck,
 
Jon Rosen