Re: IF-Clause in standard SQL

you may also want to look at ceiling or floor in teh select part of teh statement. It can be useful for this sort of thing.

Geoff

Geoff Reader

Alexander Kaltenbrunner wrote:
>
> 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 - 09:13:14 CST