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

Question

So here is the story,

SELECT STOCK_TABLE.Product_Name
From STOCK_TABLE

inside Product_Name contain:

shirt
jeans
cap
T-Shirt

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?

Show us an input and the output – neshkeev Aug 14 '14 at 09:05 — neshkeev, Aug 14 '14 at 09:05

score 2 · Answer 1 · answered Aug 14 '14 at 08:54

2

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"

answered Aug 14 '14 at 08:54

Raphaël Althaus

59,727
6
96
122

Just try it right away and it works, you know, its a really big big big help. i appreciate it. THANK YOU SO MUCH! – Ekynaldi Eky Aug 14 '14 at 08:59
`DECODE()` is [_not_ a nice function](http://stackoverflow.com/q/13712763/458741), completely agree with the CASE statement. – Ben Aug 14 '14 at 09:02
well yeah actually i often use decode because in my company software when i type in the query editor, the "DECODE" words is changing to another color and when i try to type WHEN, it doesn't change to any color just black, so im afraid it will not work (IM A FRESH NEWBIE anyway). but when many people said that we can use case just like @RaphaëlAlthaus said, i just try it and it works, so the problem is solved now, the source data name is same with the others data. THANK YOU GUYS. anyway if you don't mind to answer, in what case that we really need to use decode? – Ekynaldi Eky Aug 14 '14 at 09:16

score 2 · Answer 2 · answered Aug 14 '14 at 09:09

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

score 0 · Answer 3 · answered Aug 14 '14 at 09:08

0

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

answered Aug 14 '14 at 09:08

neshkeev

6,280
3
26
47

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

3 Answers3