SQL decoding of null values

Question

I have the following query. When type_id is null , I want it to be grouped by the name "unknown".
How can i do this.
I know there is a decoding function, but I'm not sure how to use it.

 select type_id, name from test_table group by decode(type_id,'Unknown'), name; 

How can i do this?

Answer 1

 select decode(type_id, null, 'Unknown', type_id), name, count(*) from ( select 'asdf' type_id, 'name1' name from dual union all select 'asdf' type_id, 'name2' name from dual union all select null type_id, 'name3' name from dual ) test_table group by type_id,name; 

I agree with @sql_mommy that CASE will probably look better. But I do not agree that you are using TechOnTheNet as your primary source of information. You are usually better off with official documentation, and the DECODE page is a good example of why.

DECODE has some weird behavior: "In the DECODE function, Oracle considers two zeros equal." This behavior is not mentioned in the TechOnTheNet article.

Answer 2

For null, we have an NVL function. It can be used as follows

 select nvl(type_id,'Unknown'),name from test_table group by type_id,name; 

Answer 3

You can use either the NVL function or COALESCE :

 select NVL(type_id, 'Unknown') AS type_id, name from test_table group by NVL(type_id, 'Unknown'), name; 

or

 select COALESCE(type_id, 'Unknown') AS type_id, name from test_table group by COALESCE(type_id, 'Unknown'), name; 

COALESCE more efficient than NVL because it evaluates only the second argument if the first is NULL, while the NVL evaluates both arguments each time.

Hope this helps ...