CODEWITHSUNDEEP
sqldatabaseoracle-database
valera_177
valera_177

Reputation: 7

How to set flag based on values in previous columns in same table ? (Oracle)

I'm creating a new table and carrying over several columns from a previous table. One of the new fields that I need to create is a flag that will have values 0 or 1 and value needs to be determined based on 6 previous fields in the table.

The 6 previous columns have preexisting values of 0 or 1 stored for each one. This new field needs to check whether any of the 6 columns have 1 and if so set the flag to 0. If there is 0 in all 6 fields then set itself to 1.

Hopefully this makes sense. How can I get this done in oracle? I assume a case statement and some sort of forloop?

Upvotes: 0

Views: 531

Answers (2)

Alex Poole
Alex Poole

Reputation: 191275

You can use a virtual column with a case expression; something like:

flag number generated always as (
  case when val_1 + val_2 + val_3 + val_4 + val_5 + val_6 = 0 then 1 else 0 end
) virtual

db<>fiddle

or the same thing with greatest() as @Sayan suggested.

Using a virtual column means the flag will be right for newly-inserted rows, and if any of the other values are updated; you won't have to recalculate or update the flag column manually.

I've assumed the other six columns can't be null and are constrained to only be 0 or 1, as the question suggests. If they can be null you can add nvl() or coalesce() to each term in the calculation.

Upvotes: 2

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

You can use greatest() function: GREATEST

create table t_new
as
select
   case when greatest(c1,c2,c3,c4,c5,c6)=1 -- at least one of them contains 1
          then 0
          else 1
   end c_new
from t_old;

Or even shorter:

create table t_new
as
select
   1-greatest(c1,c2,c3,c4,c5,c6) as c_new
from t_old;

In case of greatest = 1, (1-1)=0, otherwise (1-0)=1

Upvotes: 2

Related Questions