CODEWITHSUNDEEP
sqloracle-databaseif-statementplsqlsql-update
Andrea Ianni
Andrea Ianni

Reputation: 839

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

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!

Upvotes: 4

Views: 3531

Answers (3)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

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.

Upvotes: 2

Hawk
Hawk

Reputation: 5170

Conditional control IF-THEN-ELSE STATEMENT is one statement in PL/SQL. Each statement in PL/SQL ends with semi color. Hence it's written like this:

IF condition THEN
   {...statements to execute when condition is TRUE...}

ELSE
   {...statements to execute when condition is FALSE...}

END IF;  --semi colon at the end

In your code, there is an update statement inside. Therefore, at the end of this statement there must be semi colon. Your code can be written like this:

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

Some answer already suggested doing that in pure SQL rather than PL/SQL. Which I agree with.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520888

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

Upvotes: 6

Related Questions