CODEWITHSUNDEEP
sqloracle-databasedecode
Raghav
Raghav

Reputation: 215

DECODE Oracle SQL

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.

Upvotes: 3

Views: 16920

Answers (4)

Surendra
Surendra

Reputation: 29

Here below I found to use of decode with multiple condition:

Suppose based on id we need to show like below:

  1. DEV
  2. STAGE

  3. PROD

    select env_id, decode (env_id, 1, 'DEV', 2, 'STAGE', 3,'PROD') as env_name from ((select 1 env_id from dual) union (select 2 env_id from dual) union (select 3 env_id from dual));

Output will be:

enter image description here

Upvotes: 0

Truong Giang Huynh
Truong Giang Huynh

Reputation: 1

Here is an example:

CREATE TABLE SalesReps
(
    SalesRep    NUMBER(10)      NOT NULL,
    Name        VARCHAR2(15)    NOT NULL,
    Age         NUMBER(3),
    RepOffice   NUMBER(10),
    Title       VARCHAR2(15),
    HireDate    DATE            NOT NULL,
    Manager     NUMBER(10),
    Quota       NUMBER(10),
    Sales       NUMBER(12,2)    NOT NULL,
    CONSTRAINT SalesrepsPK PRIMARY KEY (Salesrep)
);

Question:

An incident occured around: 1988-05-01!

Unfortunately, we are not sure if this date is accurate! (OH NO!) We definitely know it was within 2 months (plus or minus) from that date. Find all the SalesReps who were hired during this period, so we can continue our investigation. You CANNOT use the keywords: BETWEEN, AND, OR. Instead, you must use single-line functions to solve the problem. Display the sales rep name and their hire date.

Solution:

1-Using DECODE

SELECT Name, HireDate
FROM SalesReps
WHERE 'TRUE' = (
DECODE(SIGN(HireDate - ADD_MONTHS(TO_DATE('1988-05-01','YYYY-MM-DD'),2)),
        0,'TRUE',  
       -1,DECODE(SIGN(HireDate - ADD_MONTHS(TO_DATE('1988-05-01','YYYY-MM-DD'),-2)),
                  0,'TRUE',
                  1,'TRUE',   
                  'FALSE'),
       'FALSE')
 );

2-Using CASE WHEN THEN ELSE

SELECT Name, HireDate 
FROM SalesReps
WHERE 'TRUE' = (
    CASE WHEN HireDate >= to_date('1988-03-01','YYYY-MM-DD') THEN 
                CASE WHEN HireDate <= to_date('1988-07-01','YYYY-MM-DD') THEN 'TRUE'
                     ELSE 'FALSE'
                END
          ELSE 'FALSE'
    END
);

Upvotes: 0

Multisync
Multisync

Reputation: 8787

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

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

DECODE(promo_cost,10000, DECODE(promo_category, 'G1', promo_cost *.25, NULL), NULL)

= CASE WHEN promo_cost = 10000 THEN DECODE(promo_category, 'G1', promo_cost *.25, NULL) ELSE NULL END

DECODE(promo_category, 'G1', promo_cost *.25, NULL)

= CASE WHEN promo_category = 'G1' THEN promo_cost *.25 ELSE NULL END

DECODE is similar to CASE, but usually more compact. However, CASE may have 2 forms:

  1. CASE exp1 WHEN exp2 THEN res1 ELSE res2 END (if exp1 = exp2 then res1 else res2)
  2. CASE WHEN conditon1 THEN res1 ELSE res2 END (if condition1 then res1 else res2)

DECODE is the first form of CASE

In addition,
- you can compare NULL with DECODE: decode(col, NULL, 'undefined');
- types of arguments can be different in DECODE (some rules still exist) while in CASE all exps & results should be from the same type group. For example:

select case 1 when '1' then 'OK' end from dual; -- ERROR

select decode(1, '1', 'OK') from dual; -- OK

Upvotes: 0

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

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

Upvotes: 10

Related Questions