Problem with Computed Column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problem with Computed Column

Author

Topic

hdv212
Posting Yak Master

140 Posts

Posted - 2008-05-19 : 01:15:58

hi
i want to create a table that has a computed column like this :

create table resources(
id int identity(0,1) primary key,
currentDate int not null,
currentMonth int default 0,
monthBefore as (currentMonth - (select top 1 currentMonth from resources where (currentDate - resources.currentDate) = 1)))

as u can see, monthBefore is computed column, and i want get currentMonth value of previous month, for this work, i define a column as currentDate that hold only year+month (like 971,082,083,...) and by this expression, i want to get currentMonth of previous record (previous month), but the following error has shown me :

Subqueries are not allowed in this context. Only scalar expressions are allowed.

how to solve this problem to get currentMonth of previous record ?
thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-19 : 01:28:58

If you want to use subquery to set this value i think you might need a trigger on this table for insert,update to set the value of monthBefore.

hdv212
Posting Yak Master

140 Posts

Posted - 2008-05-19 : 01:36:59

thanks visakh16
you mean that only use trigger ?
not other solution(s) ?
can u write this expression for me ?
thanks

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-05-19 : 03:08:26

Hi HDV, u dont need a trigger u can use a computed column which uses a funtion
try some thing like this

create table resources(
id int identity(0,1) primary key,
currentDate int not null,
currentMonth int default 0
)
GO

create function dbo.udf_GetMonthBefore
(
@Id int
)
returns int
as
begin

declare @MonthBefore int
select @MonthBefore = currentMonth - (select top 1 currentMonth
from resources
where (currentDate - resources.currentDate) = 1)
from resources
where id = @id

return @MonthBefore
end
GO

alter table resources add monthBefore as (dbo.udf_GetMonthBefore(id))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources