| Decode used in conjunction with sign [message #686105] |
Fri, 10 June 2022 16:04  |
Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
I'm new to SQL, and I understand the sign function and the decode function. However, I'm analyzing a procedure, and I'm having a hard time understanding what the sign function does in this code. This is the line I'm trying to figure out:
decode(sign(to_number(to_char(proposed_bill_dt,'YYYYMM')) - l_prod_date_less2),0,net_amt,0)
proposed_bill_dt is a date, could be in the past or the future.
l_prod_date_less2 is the sysdate minus 2 months.
My understanding of decode, in context to this line of code, is that if the sign comes back 0, then it uses the net_amt, else it's 0. But, doesn't 0 mean it's equal?
|
|
|
|
| Re: Decode used in conjunction with sign [message #686108 is a reply to message #686105] |
Fri, 10 June 2022 16:13  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Huh? "I'm new to SQL"? Who is? Solomon Yakobson I know certainly isn't, so ... what is it about?
Anyway, this code doesn't make sense. It is subtracting NUMBER - DATE, and that's invalid, e.g.
SQL> select 202206 - date '2022-04-10' from dual;
select 202206 - date '2022-04-10' from dual
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got DATE
proposed_bill_dt - l_prod_date_less2
if SIGN(proposed_bill_dt - l_prod_date_less2) = 0 (i.e. they are equal) then take NET_AMT,
else (if they aren't equal) take 0
[Updated on: Fri, 10 June 2022 16:19] Report message to a moderator |
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
