Computed Column Expression not working

Question

Computed Column Expression not working

SteveD SQL

SSCrazy

Points: 2261
More actions
February 4, 2016 at 3:48 am

#311251

Hi All,
In most of my tables I have Active and Inactive columns as most of the time I can DELETE record I just need to 'inactivate' them.
I was trying to create a Computed Column to save me the trouble of having to use the 'IsActive' expression in all my SQL statements. The following tables definition gives me an error on the > sign (incorrect syntax near) and GETDATE() (expecting '(' )
CREATE TABLE TestComputedColumn
(
Active Date DEFAULT CONVERT(date,GETDATE()) NOT NULL
,Inactive Date NULL
,IsActive AS ( ISNULL(Inactive,CONVERT(date,'2999-12-31')) > CONVERT(date,GETDATE()) )
)
Thanks
Steve

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

SteveD SQL (2/4/2016)
Hi All,
In most of my tables I have Active and Inactive columns as most of the time I can DELETE record I just need to 'inactivate' them.
I was trying to create a Computed Column to save me the trouble of having to use the 'IsActive' expression in all my SQL statements. The following tables definition gives me an error on the > sign (incorrect syntax near) and GETDATE() (expecting '(' )
CREATE TABLE TestComputedColumn
(
Active Date DEFAULT CONVERT(date,GETDATE()) NOT NULL
,Inactive Date NULL
,IsActive AS ( ISNULL(Inactive,CONVERT(date,'2999-12-31')) > CONVERT(date,GETDATE()) )
)
Thanks
Steve

You've only got part of your expression there:

( ISNULL(Inactive,CONVERT(date,'2999-12-31')) > CONVERT(date,GETDATE()) )

which could be read as something like this:

IsActive AS 1 > 0

What do you want IsActive to evaluate to when 1 > 0?

And when NOT 1 > 0?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Yes, you have a Boolean expression there, which T-SQL doesn't evaluate. You need to use a CASE expression to specify what values you want for active and inactive - something like this:

CREATE TABLE TestComputedColumn (

Active date DEFAULT CONVERT(date,GETDATE()) NOT NULL

,Inactive date NULL

,IsActive ASCASE

WHEN ISNULL(Inactive,'2999-12-31') > CONVERT(date,GETDATE()) THEN 'Active' -- or 1

ELSE 'Inactive' -- or 0

END

)

John

SteveD SQL SSCrazy Points: 2261 More actions · Answer 3

Thank to you both for the quick reply.

Didn't realise I needed to use a CASE as the values I was are (0 or 1) so assumed that would work.

Thanks again