Path: utzoo!utgpu!news-server.csri.toronto.edu!rpi!usc!elroy.jpl.nasa.gov!ncar!gatech!mcnc!rti!mozart!kent
From: kent@manzi.unx.sas.com (Paul Kent)
Newsgroups: comp.databases
Subject: Re: How do I make this class of report?
Message-ID: <1991May10.172137.8804@unx.sas.com>
Date: 10 May 91 17:21:37 GMT
References: <1991May8.162003.15605@mck-csc.mckinsey.com> <24226@oolong.la.locus.com>
Sender: news@unx.sas.com (Noter of Newsworthy Events)
Organization: SAS Institute Inc.
Lines: 57
Nntp-Posting-Host: manzi.unx.sas.com


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.