Re: Totally lost on this function

amerar_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
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org