Re: SQL Select

From: terryg8 <trg_at_ibm.net>
Date: 1997/07/15
Message-ID: <33CBF6C9.1997@ibm.net>#1/1

Alain Gagne wrote:
>
> I have a little problem with a select statement.
> I want to be able to put all these selects into only one select.
> Is someone can tell me the good syntax please?
>
> SELECT PT06.NO_DOSS_IR as doss1, sum(PT06.MNT_IMPUT) AS Total1995
> FROM PT06
> WHERE PT06.DAT_EVEN_ORIG_IR='1995'
> GROUP BY PT06.NO_DOSS_IR
>
> SELECT PT06.NO_DOSS_IR as doss2, sum(PT06.MNT_IMPUT) AS Total1996
> FROM PT06
> WHERE PT06.DAT_EVEN_ORIG_IR='1996'
> GROUP BY PT06.NO_DOSS_IR
>
> SELECT PT06.NO_DOSS_IR as doss3, sum(PT06.MNT_IMPUT) AS Total1997
> FROM PT06
> WHERE PT06.DAT_EVEN_ORIG_IR='1997'
> GROUP BY PT06.NO_DOSS_IR;
>
> The final result must be like this:
>
> DOSS1 DOSS2 DOSS3 TOTAL1995 TOTAL1996 TOTAL1997
> ________________________________________________________
>
> The order of the columns is not important. But it is very important
> that I have all these columns into one result
>
> Thank you
> Alain Gagne
> Alain.Gagne_at_cgi.ca
> Alain Gagne
> CGI inc.

Take a look at the decode statement and try something like select .. decode(PT06.NO_DOSS_IR,'1995',PT06.no_doss_ir,0) as doss1,

          sum(decode(PT06.NO_DOSS_IR,'1995',PT06.no_doss_ir,0)) as total1995,

          decode(PT06.NO_DOSS_IR,'1996',PT06.no_doss_ir,0) as doss2,
          sum(decode(PT06.NO_DOSS_IR,'1996',PT06.no_doss_ir,0)) as

total1996,

    .
    .
    .

etc.
You should be able to get what you want after playing with it a little. Received on Tue Jul 15 1997 - 00:00:00 CDT