Re: IF-Clause in standard SQL

In some cases you my use DECODE function. But for common purposes you may write function.

Alexander Kaltenbrunner <alexkalt_at_gmx.at> ÐÉÛÅÔ × ÓÏÏÂÝÅÎÉÉ:3a9d1886$0$26348_at_SSP1NO25.highway.telekom.at...
> Hello!
>
> I have a litte problem with the SQL-Language
>
> I have the following Table in my database:
>
> IDENTNR DATE AMOUNT
> 1000.00 20000801 100
> 1000.00 20000802 200
> 1000.01 20000901 200
> 1000.02 20000928 100
> 1000.02 20001001 200
> 1000.00 20001001 200
>
> I have also a querry that looks like the following
>
> SELECT
> DUAL.IDENTNR,
> SUM(DECODE(SUBSTR(DUAL.DATE,1,6),'200008',DUAL.AMOUNT)) PREV_MONTH,
> SUM(DECODE(SUBSTR(DUAL.DATE,1,6),'200009',DUAL.AMOUNT)) ACT_MONTH,
> SUM(DECODE(SUBSTR(DUAL.DATE,1,6),'200010',DUAL.AMOUNT)) NEXT_MONTH
> FROM
> DUAL
> GROUP BY
> IDENTNR
> ORDER BY
> IDENTNR;
>
> The result is here:
>
> IDENTNR PREV_MONTH ACT_MONTH NEXT_MONTH
> 1000.00 300 200
> 1000.01 200
> 1000.02 100 200
>
> But what could I do if I want to sum up everything bevore one date? Is
 ther
> a possibility of a if-clause in standard SQL like
>
> SUM(IF(SUBSTR(DUAL.DATE,1,6)<'200008',DUAL.AMOUNT)) BEFORE,
> SUM(IF(SUBSTR(DUAL.DATE,1,6)>'200010',DUAL.AMOUNT)) AFTER
>
> ?
>
> Every answer would be apprechiated!
>
>
Received on Thu Mar 01 2001 - 01:08:16 CST