DECODE Oracle SQL

Question

I recently came across few decode queries.

I found this decode function somewhat confusing. here are the queries:

SELECT promo_id,
       DECODE(NVL(promo_cost,0),promo_cost, promo_cost * 0.25, 100) "Discount"
FROM promotions; 

SELECT promo_id,
       DECODE(promo_cost,10000, DECODE(promo_category, 'G1', promo_cost *.25, NULL), NULL) "Catcost";

I went through the internet, studied some articles but it seems that DECODE is something that varies per query.

Could anyone please explain to me what these two queries are doing.

Thank you very much.

Note: this is not my homework. I am a Musician by profession just took C and SQL recently as it is so interesting.

Answer 1

The syntax for DECODE function is DECODE( expression , search , result [, search , result]... [, default] ).

In your first query :

DECODE(NVL(promo_cost,0),promo_cost, promo_cost * 0.25, 100)

The following is the interpretation :

expression = NVL(promo_cost,0)
search     = promo_cost
result     = promo_cost * 0.25
default    = 100

So, it means,

IF NVL(promo_cost,0) = promo_cost
  THEN 
     output = promo_cost * 0.25
ELSE
     output = 100
END

The same could be interpreted using CASE as well :

CASE 
   WHEN NVL(promo_cost,0) = promo_cost 
   THEN 
         promo_cost * 0.25
   ELSE 
      100 
END