hin@mck-csc.mckinsey.com (John K. Hinsdale) (05/08/91)
[Sorry if this is the wrong newsgroup for this] Hello netland, I have a rather simple question about how to generate a particular class of report from an SQL-based database (ours happens to be 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: Take a table with columns NAME, YEAR, SALARY, that looks like: NAME YEAR SALARY ----- ---- ------ Callahan 88 20000 Callahan 89 23000 Callahan 90 27000 Muldoon 88 15300 Muldoon 89 20000 O'Connor 89 30000 O'Connor 90 34000 I'd like to generate a report that shows the salary history across the page, like so: 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. Anybody out there know of a better tool, or perhaps an SQL workaround for this kind of report? It seems like a *really* common thing one needs to do. Please E-mail, and I promise I'll post the best answer(s) back here in about a week. AtDhVaAnNkCsE, John K. Hinsdale hin@mckinsey.com
jfr@locus.com (Jon Rosen) (05/10/91)
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
lugnut@sequent.UUCP (Don Bolton) (05/10/91)
In article <1991May8.162003.15605@mck-csc.mckinsey.com> hin@mck-csc.mckinsey.com (John K. Hinsdale) writes: >[Sorry if this is the wrong newsgroup for this] ORACLE sqlplus has a function called DECODE you should check out, it should do as you require with a bit of coding on your part. > >Hello netland, I have a rather simple question about how to generate a >particular class of report from an SQL-based database (ours happens >to be 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: > >Take a table with columns NAME, YEAR, SALARY, that looks like: > >NAME YEAR SALARY >----- ---- ------ >Callahan 88 20000 >Callahan 89 23000 >Callahan 90 27000 >Muldoon 88 15300 >Muldoon 89 20000 >O'Connor 89 30000 >O'Connor 90 34000 > >I'd like to generate a report that shows the salary history >across the page, like so: > >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. > >Anybody out there know of a better tool, or perhaps an SQL workaround >for this kind of report? It seems like a *really* common thing one >needs to do. Please E-mail, and I promise I'll post the best >answer(s) back here in about a week. > >AtDhVaAnNkCsE, > >John K. Hinsdale >hin@mckinsey.com
kent@manzi.unx.sas.com (Paul Kent) (05/11/91)
In article <24226@oolong.la.locus.com>, jfr@locus.com (Jon Rosen) writes: >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 > > 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... > Jon complains about the verbosity of CASE (and he didnt get it verbose enuff, you need to end the CASE construct with END, although he might have saved a few keystrokes by leaving out the OTHERWISE ;-) (OTHERWISE is ELSE in my feb-1990 version of the draft has that changed?) funny how the CASE syntax "had" to have a "short-cut" so it could be used like a "c" select expression too.. CASE year WHEN 1988 THEN salary WHEN 1989 THEN salary+2 END but most of the time an IF() function would have done nicely. the SQL2 language has an excessive (IMHO) bent towards wordiness anybody out there figured out all that CASTING stuff? by the way, PROC SQL (part of the SAS system) does support many of the new language features in the draft sql standard. CASE, the outer forms of joins, UNION/INTERSECT/EXCEPT, and the freedom to have a <subquery> most anywhere you used to be be restricted to a variable. boolean expressions are allowed anywhere, so the selection of YEAR=88 can be done with... select emp, sum( (year=88)*salary ) as yr88, sum( (year=89)*salary ) as yr89 ... but as jon points out, it is still very clumsy to generalise this to an unknown number of years. its been a while since i attended an ANSI-sql meeting, would anyone with the list of dates drop me a note, as i'd like to go to another sometime soon. -- Paul Kent (SQL r&d) " nothing ventured, nothing disclaimed " kent@unx.sas.com SAS Institute Inc, SAS Campus Dr, Cary NC 27513-2414.
jsw@vipunen.hut.fi (Jaakko S Wallenius) (05/27/91)
In article <1991May8.162003.15605@mck-csc.mckinsey.com> hin@mck-csc.mckinsey.com (John K. Hinsdale) writes:
......
=> Take a table with columns NAME, YEAR, SALARY, that looks like:
=>
=> NAME YEAR SALARY
=> ----- ---- ------
=> Callahan 88 20000
=> Callahan 89 23000
=> Callahan 90 27000
=> Muldoon 88 15300
=> Muldoon 89 20000
=> O'Connor 89 30000
=> O'Connor 90 34000
=>
=> I'd like to generate a report that shows the salary history
=> across the page, like so:
=>
=> NAME 88 89 90
=> ------ ------ ------ ------
=> Callahan 20000 23000 27000
=> Muldoon 15300 20000 <null>
=> O'Connor <null> 30000 34000
Following query shold do the thing (with ORACLE)
select NAME,
decode(YEAR,88,SALARY,null) Y-88,
decode(YEAR,89,SALARY,null) Y-89,
decode(YEAR,90,SALARY,null) Y-90
from TABLE
group by NAME;
=> John K. Hinsdale
=> hin@mckinsey.com
Jaakko Wallenius
Helsinki University of Technology, Finland
Jaakko_Wallenius@hut.fi