CODEWITHSUNDEEP
sql-servert-sqlddlcalculated-columns
sianeczniak
sianeczniak

Reputation: 11

How to create table with computed column and if statement?

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!

Upvotes: 0

Views: 852

Answers (2)

Martin Smith
Martin Smith

Reputation: 453327

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.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

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.

Upvotes: 2

Related Questions