Re: Urgent! Programming Question ,(value of current date/value of prior date)

From: Matthias Wirtz <Matthias.Wirtz_at_epost.de>
Date: Mon, 29 Dec 2003 19:25:28 -0500
Message-ID: <bsqgp8$ch3a$2@ID-151394.news.uni-berlin.de>

"Ken Denny" <ken_at_kendenny.com> wrote
news:Xns945C9A3F1FDE8kendenny_at_216.77.188.18...
> "Coca" <bigmelon_at_btamail.net.cn> wrote in
> news:bsf2h3$ci628$2_at_ID-189205.news.uni-berlin.de:

> Select to_char(a1.sdate,'YYYY-MM-DD') a1.svalue/a2.svalue indicator1
> from account a1, account a2
> where a1.sdate between &begin_date and &end_date
> and trunc(a2.sdate)(+) = trunc(add_months(trunca1.sdate,-1));

I think there is a comma and parathesis missing. I tried it with:

select to_char(a1.sdate,'YYYY-MM-DD'), a1.svalue/a2.svalue indicator1 from account a1, account a2
where a1.sdate between &begin_date and &end_date and trunc(a2.sdate)(+) = trunc(add_months(trunc(a1.sdate),-1));

But I got an error:

where trunc(a2.sdate) (+) = trunc(add_months(trunc(a1.sdate),-1))

                        *

ERROR in row 3:
ORA-00936: missing expression

I don't know what oracle is complaining about.

Nevertheless to avoid the self join you can use the LAG function:

select sdate, svalue, decode(lag(svalue, 1, 0) over (order by sdate), 0, null, svalue/lag
(svalue, 1, 0) over (order by sdate)) as indicator from account;

--
Matthias Wirtz  -  Norfolk, USA

Received on Mon Dec 29 2003 - 18:25:28 CST