Re: Totally lost on this function
Date: Mon, 31 Dec 2007 20:11:20 -0800 (PST)
Message-ID: <6e30dabb-6e43-40a8-a3db-480314592643@t1g2000pra.googlegroups.com>
On Dec 31, 7:19 pm, "ame..._at_iwc.net" <ame..._at_iwc.net> wrote:
> 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
> 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!
Maybe it is best to look at a simple version of what you posted, and
see how the values change as the input changes:
SELECT
MONTH_END,
TO_DATE(SD.MONTH_END,'MM') C1,
LAST_DAY(TO_DATE(SD.MONTH_END,'MM')) C2,
MONTHS_BETWEEN(LAST_DAY(TO_DATE(SD.MONTH_END,'MM')),SYSDATE) C3,
SIGN(MONTHS_BETWEEN(LAST_DAY(TO_DATE(SD.MONTH_END,'MM')),SYSDATE))
C4
FROM
(SELECT
TO_CHAR(LEVEL,'00') MONTH_END
FROM
DUAL
CONNECT BY
LEVEL<=12) SD;
The inline view simply counts from 01 to 12, while the outer SELECT
shows each stage of the calculation.
With SYSDATE at 31-DEC-2007 the output looks like this:
MON C1 C2 C3 C4
--- --------- --------- ----- ---- 01 01-JAN-07 31-JAN-07 -11 -1 02 01-FEB-07 28-FEB-07 -10 -1 03 01-MAR-07 31-MAR-07 -9 -1 04 01-APR-07 30-APR-07 -8 -1 05 01-MAY-07 31-MAY-07 -7 -1 06 01-JUN-07 30-JUN-07 -6 -1 07 01-JUL-07 31-JUL-07 -5 -1 08 01-AUG-07 31-AUG-07 -4 -1 09 01-SEP-07 30-SEP-07 -3 -1 10 01-OCT-07 31-OCT-07 -2 -1 11 01-NOV-07 30-NOV-07 -1 -1 12 01-DEC-07 31-DEC-07 0 0
Column C4 shows the value of the SIGN function - if the months between is less than 0, SIGN returns -1; if it is greater than 0, SIGN returns 1; if it is equal to 0, SIGN returns 0.
Let's try again with SYSDATE set to 10-JUN-2008
MON C1 C2 C3 C4 --- --------- --------- ---------- --- 01 01-JAN-08 31-JAN-08 -4.3532501 -1 02 01-FEB-08 29-FEB-08 -3.4177662 -1 03 01-MAR-08 31-MAR-08 -2.3532501 -1 04 01-APR-08 30-APR-08 -1.3855081 -1 05 01-MAY-08 31-MAY-08 -.35325007 -1 06 01-JUN-08 30-JUN-08 .614491861 1 07 01-JUL-08 31-JUL-08 1.64674993 1 08 01-AUG-08 31-AUG-08 2.64674993 1 09 01-SEP-08 30-SEP-08 3.61449186 1 10 01-OCT-08 31-OCT-08 4.64674993 1
11 01-NOV-08 30-NOV-08 5.61449186 1
12 01-DEC-08 31-DEC-08 6.64674993 1 The values in column C3 possibily do not show the values that were intended when the original SQL statement was constructed, as SIGN never returns 0.
The first DECODE in your SQL statement basically states:
* If the last day of the SD.MONTH_END month is greater than today's
date, output the value that follows the 1:
{TO_CHAR(LAST_DAY(TO_DATE(SD.MONTH_END,'MM')-1*365),'MM/DD/RR')}
* If the last day of the SD.MONTH_END month is equal to today's date,
output the value that follows the 0:
{TO_CHAR(LAST_DAY(TO_DATE(SD.MONTH_END,'MM')-0*365),'MM/DD/RR')}
* If the last day of the SD.MONTH_END month is less than today's date,
output the value that follows the -1:
{TO_CHAR(LAST_DAY(TO_DATE(SD.MONTH_END,'MM')-0*365),'MM/DD/RR')}
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Mon Dec 31 2007 - 22:11:20 CST