Re: Totally lost on this function

On Dec 31, 5:52 pm, DA Morgan <damor..._at_psoug.org> wrote:
> ame..._at_iwc.net wrote:
> > Hi,
>
> > We have a query in one of our stored procedures, which I think may
> > have a logic bug in it given the dta it selects.
>
> > However, I do not understand this SIGN function and what its purpose
> > is here.......can someone help me and explain it to me??
>
> > Thanks in advance:
>
> > select
> > mt.COMP_NAME,mt.TICKER,nvl(to_char(to_date(sd.MONTH_end,'mm'),'Month'),'n/
> > a'),decode( sign(months_between(last_day(to_date(sd.MONTH_end,'mm')),
> > sysdate)),1,
> >        to_char(last_day(to_date(sd.MONTH_end,'mm')-1*365),'mm/dd/rr'),
> > 0,to_char(last_day(to_date(sd.MONTH_end,'mm')-0*365),'mm/dd/rr'),-1,
> >        to_char(last_day(to_date(sd.MONTH_end,'mm')-0*365),'mm/dd/
> > rr')),decode( sign(months_between(last_day(to_date(sd.MONTH_end,'mm')),
> > sysdate)),1,
> >        to_char(last_day(to_date(sd.MONTH_end,'mm')-2*365),'mm/dd/rr'),
> > 0,to_char(last_day(to_date(sd.MONTH_end,'mm')-1*365),'mm/dd/rr'),-1,
> >        to_char(last_day(to_date(sd.MONTH_end,'mm')-1*365),'mm/dd/
> > rr')),decode( sign(months_between(last_day(to_date(sd.MONTH_end,'mm')),sysdate)),
> > 1,
> >        to_char(last_day(to_date(sd.MONTH_end,'mm')-3*365),'mm/dd/rr'),
> > 0,to_char(last_day(to_date(sd.MONTH_end,'mm')-2*365),'mm/dd/rr'),-1,
> >        to_char(last_day(to_date(sd.MONTH_end,'mm')-2*365),'mm/dd/
> > rr')),decode( sign(months_between(last_day(to_date(sd.MONTH_end,'mm')),
> > sysdate)),1,
> >        to_char(last_day(to_date(sd.MONTH_end,'mm')-4*365),'mm/dd/rr'),
> > 0,to_char(last_day(to_date(sd.MONTH_end,'mm')-3*365),'mm/dd/rr'),-1,
> >        to_char(last_day(to_date(sd.MONTH_end,'mm')-3*365),'mm/dd/
> > rr')),decode( sign(months_between(last_day(to_date(sd.MONTH_end,'mm')),
> > sysdate)),1,
> >        to_char(last_day(to_date(sd.MONTH_end,'mm')-5*365),'mm/dd/
> > rr'),   0,to_char(last_day(to_date(sd.MONTH_end,'mm')-4*365),'mm/dd/
> > rr'),-1,
> >        to_char(last_day(to_date(sd.MONTH_end,'mm')-4*365),'mm/dd/rr'))
> > FROM..........
> > WHERE.........
>
> Look at the demo here:http://www.psoug.org/reference/number_func.html#nsig
>
> Then look at how it is used in DECODE here:http://www.psoug.org/reference/decode_case.html
>
> Hopefully these demos will clear it up.
> --
> Daniel A. Morgan
> Oracle Ace Director & Instructor
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

Daniel,

I'm just totally lost in the who decode, sign, between, -1.....blah, blah.......I'm lost.

I'm trying to understand this in english, like: It takes the MONTH_END column, checks if it is between.....and if it is......and if it is not........

Thanks! Received on Mon Dec 31 2007 - 18:19:11 CST