IF-ELSE statement: create a column depending on another one

Question

In the table JAN07, I have a column TEMPO_INTERCORSO (number(10,0)) and I want to create another column ANTE_POST (number(1)) that is '0' when TEMPO_INTERCORSO > 0 and '1' otherwise.

IF (TEMPO_INTERCORSO > 0) THEN
   UPDATE JAN07 SET ANTE_POST = 1
ELSE
   UPDATE JAN07 SET ANTE_POST = 0
END IF;

I've tried a lot of different ways that you can find on the web (for example the ";" before the ELSE: sometimes there is, sometimes not), but it is still not working.

IF (TEMPO_INTERCORSO = 0) THEN
Report error -
Comando sconosciuto

Some ideas?

ps= I've tried with JAN07.TEMPO_INTERCORSO too!

Answer 1

The following UPDATE query uses CASE...WHEN to achieve what you want:

UPDATE JAN07
SET ANTE_POST = CASE WHEN TEMPO_INTERCORSO > 0 THEN 1 ELSE 0 END

Answer 2

I would rather suggest Virtual Columns introduced in Oracle Database 11g Release 1. A simple CASE statement would do the rest.

For example,

SQL> CREATE TABLE t
  2    (
  3      TEMPO_INTERCORSO NUMBER,
  4      ANTE_POST        NUMBER GENERATED ALWAYS AS (
  5      CASE
  6        WHEN TEMPO_INTERCORSO > 0
  7        THEN 1
  8        ELSE 0
  9      END) VIRTUAL
 10    );

Table created.

Now, you need not worry about manually updating the virtual column, it will be automatically generated at run time.

Let's insert the values only in static column and see:

SQL> INSERT INTO t(TEMPO_INTERCORSO) VALUES(0);

1 row created.

SQL> INSERT INTO t(TEMPO_INTERCORSO) VALUES(1);

1 row created.

SQL> INSERT INTO t(TEMPO_INTERCORSO) VALUES(10);

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT * FROM t;

TEMPO_INTERCORSO  ANTE_POST
---------------- ----------
               0          0
               1          1
              10          1

So, you have your column ANTE_POST with desired values automatically.