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

"Matthias Wirtz" <Matthias.Wirtz_at_epost.de> wrote in message news:<bsqgp8$ch3a$2_at_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.
>

Proper syntax would be:

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

(+) has to follow the column of outer joined table, not the expression.

> 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;
Received on Tue Dec 30 2003 - 09:49:58 CST