CODEWITHSUNDEEP
sqloracle-databasedecodedecoding
Ekynaldi Eky
Ekynaldi Eky

Reputation: 9

SQL Decode Function (i only want to decode few of them)

So here is the story, 

SELECT STOCK_TABLE.Product_Name
From STOCK_TABLE

inside Product_Name contain:

So I want to change the contain of the item inside Product_Name, as long as I know that we can use decode to face this problem, but the problem is i just want to decode some of theme (Jeans and Cap), not all. so i use:

SELECT
  DECODE(STOCK_TABLE.Product_Name, 'Jeans', 'PJ1', 'Cap','PC1') as "Product Name"
From
  STOCK_TABLE

then the result come out with just PJ1(used to be Jeans) and PC1 (Used to be Cap) only.

And the problem is I want the rest of it (T-Shirt and Shirt) also come out but with its original name.
So how can I do that thing? is that possible using decode with some function addition that I miss or we have to use another SQL function?

Upvotes: 1

Views: 1650

Answers (3)

Paul Maxwell
Paul Maxwell

Reputation: 35563

I agree that case expressions are far easier to read and maintain, but if you encounter DECODE you should understand its structure is very similar to a case expression anyway

SELECT
DECODE(STOCK_TABLE.Product_Name
                               , 'Jeans', 'PJ1'
                               , 'Cap','PC1'
                               , Product_Name
      ) as "Product Name"
From
STOCK_TABLE

OR

SELECT
DECODE(evaluate_this_expression
                               , value_to_match, value_to_output
                               , value_to_match, value_to_output
                               , else_value_to_output
      ) as alias_for_this
From
wherever

Upvotes: 2

neshkeev
neshkeev

Reputation: 6476

Try this:

SELECT CASE 
         WHEN Product_Name = 'Jeans' THEN 'PJ1'
         WHEN Product_Name = 'Cap' THEN 'PC1'
         ELSE Product_Name
       END as "Product Name" 
  FROM STOCK_TABLE

Upvotes: 0

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

DECODE is a nice function, but fast unreadable (personal point of view, of course)

You can perfectly use a CASE... WHEN (which does the same, but often easier to read when you have more than one if else clause)

case Product_Name
     when 'Jeans' then 'PJ1'
     when 'Cap' then 'PC1'
     else Product_Name
end as "Product Name"

Upvotes: 2

Related Questions