Can I use a computed column for this?

15 Sep 2005 9:03 PM

james

I have a table with two integers and I want another computed column like so

IIF ( colX >= colY, 1, 0 )

but enterprise manager keeps telling me there is an error in my Formula but
I do not see what it could be.

the computed column is defined to be an int

thanks,

J

15 Sep 2005 9:09 PM

Aaron Bertrand [SQL Server MVP]

> but enterprise manager keeps telling me there is an error in my Formula

Egads, why are you using Enterprise Manager for this? Try Query Analyzer.

CREATE TABLE dbo.foo
(
    x INT,
    y INT,
    z AS CONVERT(INT, CASE WHEN x >= y THEN 1 ELSE 0 END)
)

Also, not sure why you want to use INT. This could easily be BIT or TINYINT
if it is only ever going to contain two possible values.

19 Sep 2005 2:36 PM

James

Thanks Aaron, and all the other responses. All your comments are helpful
JIM

Show quote

"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:%23GfaOmjuFHA.2076@TK2MSFTNGP14.phx.gbl...
>> but enterprise manager keeps telling me there is an error in my Formula
>
> Egads, why are you using Enterprise Manager for this? Try Query Analyzer.
>
> CREATE TABLE dbo.foo
> (
>    x INT,
>    y INT,
>    z AS CONVERT(INT, CASE WHEN x >= y THEN 1 ELSE 0 END)
> )
>
> Also, not sure why you want to use INT. This could easily be BIT or
> TINYINT if it is only ever going to contain two possible values.
>

15 Sep 2005 9:10 PM

Alejandro Mesa

James,

Use a "case" expression.

select colX, colY, case when colX >= colY then 1 else 0 end as colZ
from t1

AMB

Show quote

"james" wrote:

> I have a table with two integers and I want another computed column like so
>
> IIF ( colX >= colY, 1, 0 )
>
> but enterprise manager keeps telling me there is an error in my Formula but
> I do not see what it could be.
>
> the computed column is defined to be an int
>
> thanks,
>
> J
>
>
>

15 Sep 2005 9:13 PM

Trey Walpole

there is no IIF() in SQL - it's CASE

case when colx>=coly then 1 else 0 end

james wrote:

Show quote

>I have a table with two integers and I want another computed column like so
>
>IIF ( colX >= colY, 1, 0 )
>
>but enterprise manager keeps telling me there is an error in my Formula but
>I do not see what it could be.
>
>the computed column is defined to be an int
>
>thanks,
>
>J
>
>
>
>

15 Sep 2005 9:14 PM

David Portas

CASE WHEN colx >= coly THEN 1 ELSE 0 END

Why would you put such a thing in a computed column? Put it in a view or
query rather than clutter your table with redundant information.

--
David Portas
SQL Server MVP
--