CODEWITHSUNDEEP
sqloracle-database
Chronicles_X0
Chronicles_X0

Reputation: 13

1 if the value is NOT NULL otherwise NULL (Oracle)

I'm creating a new table and carrying over one of the fields from a previous table. I need to set the value to 1 if value is not null otherwise leave it as NULL.

Is this possible to do without an update statement? I understand that in MSSQL you can use ISNULL function, but how about Oracle?

Upvotes: 0

Views: 713

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

Oracle offers the nvl2() function:

nvl2(col, 1, null)

If you want to change the value, though, you need to use an update or modify the data somehow.

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 143023

CASE would do that. For example:

create table new_table as
  select case when that_column is null then null else 1 end as that_column,
         other_column
  from the_original_table

Upvotes: 2

Related Questions