DECODE

I was tidying up some old E-mails recently and found this example from 2008. I think it may have been from a Spanish student who needed help with his homework. He had a table called COCHES. It contained details of car manufacturers (in the column called TIPO). Each one made cars with 3 or 5 doors. He needed to update the table with one SQL statement and change the values in the doors (PUERTAS) column from 3 to 5 and vice versa. I suggested he could do it with decode as shown below:

SQL> select * from coches;
 
TIPO          PUERTAS
---------- ----------
Austin              5
Morris              3
 
SQL> update coches set puertas = decode (puertas,3,5,5,3);
 
2 rows updated.
 
SQL> select * from coches;
 
TIPO          PUERTAS
---------- ----------
Austin              3
Morris              5
 
SQL>

Calculating Averages (Part 2)

Go to part 1

(Tested on Oracle 9 but probably not on the database used for part 1.)

In Calculating Averages (Part 1) I ran the following SQL:

select avg(num_rows) from dba_tables

where num_rows is not null;

Since then I have read that the where clause was unnecessary as Oracle does not include null values when it works out averages. I decided to see if this was true. First I checked that there were both null and not null values in the num_rows column in dba_tables:

SQL> select count(*) from dba_tables

  2  where num_rows is null;

  COUNT(*)

----------

       459

SQL> select count(*) from dba_tables

  2  where num_rows is not null;

  COUNT(*)

----------

       366

SQL>

Incidentally, you can combine the two statements above into one by using decode:

SQL> select

  2  decode(num_rows,null,'NULL','NOT NULL') row_count,

  3  count(*)

  4  from dba_tables

  5  group by decode(num_rows,null,'NULL','NOT NULL');

ROW_COUNT   COUNT(*)

--------- ----------

NOT NULL         366

NULL             459

SQL>

Then I calculated the average as before:

SQL> select avg(num_rows) from dba_tables

  2  where num_rows is not null;

AVG(NUM_ROWS)

-------------

   783867.724

SQL>

Finally, I omitted the where clause and the result was the same. This proves that avg ignores null values:

SQL> select avg(num_rows) from dba_tables;

AVG(NUM_ROWS)

-------------

   783867.724

SQL>