It has been added either in 8.1.67 or 8.1.7
--
Niall Litchfield
Oracle DBA
Audit Commission UK
"Jim Smith" <jim_at_jimsmith.demon.co.uk> wrote in message
news:z3w7mnFoChn6Ew8w_at_jimsmith.demon.co.uk...
> 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
Received on Thu Mar 01 2001 - 05:03:22 CST
