Re: IF-Clause in standard SQL

In article <3a9d1886$0$26348_at_SSP1NO25.highway.telekom.at>, Alexander Kaltenbrunner <alexkalt_at_gmx.at> writes
>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;
Why are you using DUAL as a table name? There is already a table called DUAL and having another one with multiple rows is likely to break a lot of code.
>
>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
In ANSI SQL there is a CASE statement, but oracle doesn't support it (unless it has been added recently)

>

It would be simpler if you used date types, but here goes.

The basic algorithm is to subtract the cut-off date from the date in the row and take the sign of the result. If it is negative (-1) it is before, positive (+1) is after. It is up to you whether 0 is before or after (I'll assume before).

• if date is before 200008 add amount else add 0 sum( decode ( sign ( to_number (substr (dual.date,1,6))-200008),-1, dual.amount,0)) BEFORE,
• if date is before 200008 add 0 else add amount sum( decode ( sign ( to_number (substr (dual.date,1,6))-200008),-1, 0, dual.amount)) ON_OR_AFTER,

I haven't syntax checked this, but the basic principal should work.

-- 
Jim Smith