CODEWITHSUNDEEP
sqloracle-databaseplsql
mimabe
mimabe

Reputation: 37

DECODE in ORACLE

I don't quite understand how this DECODE function will resolve itself, specifically in the pointed (==>) parameter:

DECODE
(
    SH.RESPCODE, 0, SH.AMOUNT-DEVICE_FEE,
    102, SH.NEW_AMOUNT-DEVICE_FEE,
    ==>AMOUNT-DEVICE_FEE,
    0
)

Thanks in advance for any enlightenment on what that parameter will resolve to.

Upvotes: 1

Views: 530

Answers (4)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

The comments about DECODE being equivalent to CASE are good. One thing I've found to be helpful is to format the DECODE call to make it more readable, as in:

DECODE(SH.RESPCODE,
         0,                 SH.AMOUNT-DEVICE_FEE,
         102,               SH.NEW_AMOUNT-DEVICE_FEE,
         AMOUNT-DEVICE_FEE, 0)

Share and enjoy.

Upvotes: 0

rsenna
rsenna

Reputation: 11963

DECODE is basically equivalent to a CASE expression.

So this

DECODE
(
    SH.RESPCODE, 0, SH.AMOUNT-DEVICE_FEE,
    102, SH.NEW_AMOUNT-DEVICE_FEE,
    ==>AMOUNT-DEVICE_FEE,
    0
)

is equivalent to this

CASE SH.RESPCODE
    WHEN 0 THEN SH.AMOUNT - DEVICE_FEE
    WHEN 102 THEN SH.NEW_AMOUNT - DEVICE_FEE
    WHEN AMOUNT - DEVICE_FEE THEN 0
END

Upvotes: 1

David Aldridge
David Aldridge

Reputation: 52356

It says:

If the value of SH.RESPCODE is 0, then return SH.AMOUNT-DEVICE_FEE.
Otherwise, if it's equal to 102, then return SH.NEW_AMOUNT-DEVICE_FEE.
Otherwise, if it's equal to AMOUNT-DEVICE_FEE, then return 0

As a case statement it would be:

Case SH.RESPCODE
  when 0                 then SH.AMOUNT-DEVICE_FEE
  when 102               then rSH.NEW_AMOUNT-DEVICE_FEE
  when AMOUNT-DEVICE_FEE then 0
  else null
end

There are some subtle differences with respect to treatment of NULL between the two.

Upvotes: 3

Justin Cave
Justin Cave

Reputation: 231661

In pseudo-code, this would be equivalent to the following IF statement

IF( sh.respcode = 0 )
THEN
  RETURN sh.amount-device_fee
ELSIF( sh.respcode = 102 )
THEN
  RETURN sh.new_amount-device_fee
ELSIF( sh.respcode = amount-device_fee )
THEN
  RETURN 0;
END IF;

Upvotes: 2

Related Questions