0

As in title, I'm trying to create a table on SQL Server that includes computed column with if statement. I have already created basic columns, co currently trying to add computed column using ALTER TABLE.

In statement: ALTER table zgodnosc add proba as IIF([Zmiana_1_postoj] = 0 ,-50,[Zmiana_1]) float; I got error Incorrect syntax near 'float'. I've tried lots of combinations, always getting errors. Datatypes are: Zmiana_postoj_1 - bit, Zmiana_1 - int. Does someone have a clue how to write that correctly? Thanks!

sianeczniak
  • 11
  • 5

2 Answers2

1

In SQL Server, you would use:

alter table zgodnosc add proba as (case when Imiana_1_postoj] = 0 then -50 else Zmiana_1 end) ;

No type is necessary. The additional parentheses are not necessary. I habitually include them for two reasons. First, they are needed for check constraints. Second, they do a good job of visually delimiting the expression.

If you want a particular type, you can convert/cast:

alter table zgodnosc add proba as (convert(float, (case when Imiana_1_postoj] = 0 then -50 else Zmiana_1 end))

Note: I strongly discourage the use of IIF(). It was introduced into SQL Server for backwards compatibility to MS Access. CASE is the standard method of handling conditional logic in SQL.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Get rid of the float - SQL Server will infer the datatype from the datatype of the expression.

If you replace -50 with -5E1 or -50e0 the datatype of the expression will become float 

ALTER table zgodnosc add proba as IIF([Zmiana_1_postoj] = 0 ,-50e0,[Zmiana_1]);

though you may prefer to be more explicit about it.

Personally I prefer the conciseness of IIF to CASE WHEN ELSE END. I doubt the computed column syntax is transferable to other RDBMSs irrespective of that.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • What if I want to use computed column in other computed column? I have this error: Computed column 'Doba' in table 'zgodnosc' is not allowed to be used in another computed-column definition. and I don't know if I can do anything with it – sianeczniak May 08 '20 at 12:55
  • You can't nest computed columns. If you want to reuse one in another you need to copy over the definition instead, i.e. underlying `IIF` expression in this case – Martin Smith May 08 '20 at 12:56