Home » SQL & PL/SQL » SQL & PL/SQL » Decode used in conjunction with sign (Oracle 19c)
Decode used in conjunction with sign [message #686105] Fri, 10 June 2022 16:04 Go to next message
Solomon Yakobson
Messages: 3269
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 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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
It would make sense if it looks like

proposed_bill_dt - l_prod_date_less2
as it would subtract two dates, and that's then number of days between them. Result can be negative, zero (if they are equal) or positive, and then SIGN makes sense so it (the whole code - decode along with sign) is evaluated to

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

Re: Decode used in conjunction with sign [message #686111 is a reply to message #686108] Sat, 11 June 2022 05:35 Go to previous messageGo to next message
kkaschke
Messages: 4
Registered: April 2022
Junior Member
Interesting,

I never posted this. So it looks like someone is impersonating me.

SY.
Re: Decode used in conjunction with sign [message #686112 is a reply to message #686111] Sat, 11 June 2022 05:41 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Even more interesting - I just noticed my login showed up as kkaschke. So it is possible kkaschke got logged as me. I logged out and logged back in.

SY.
Re: Decode used in conjunction with sign [message #686115 is a reply to message #686112] Sat, 11 June 2022 18:16 Go to previous message
kkaschke
Messages: 4
Registered: April 2022
Junior Member
I’m definitely not impersonating anyone, at least not intentionally. Looks like some sort that f bizarre system glitch.
Previous Topic: Directory Name with UTL_FILE.FOPEN()
Next Topic: orapki: add crt and key to wallet
Goto Forum:
  


Current Time: Thu Mar 28 13:58:14 CDT 2024