Error when creating table in SQL Server

Question

I am getting the following error:

Computed column 'OrderIssue' in table 'ProductIssue' cannot be persisted because the column is non-deterministic.

when trying to create a table with a column like

[OrderIssue]  AS 
(case 
    when [IssueID]=(1009) AND 
         ([Issuenumber]>=(400000) AND 
          [Issuenumber]<=(999999) OR 
          [Issuenumber]<(400000) AND 
          isnull(isnull([Enterdate],[Stagedate]),[lastClosingdate])<
                        dateadd(day,(0),'2013-04-01')) 
    then (0) 
    when [IssueID]=(1009) AND 
         ([Issuenumber]>=(400000) OR 
          [Issuenumber]<(700000) AND 
          isnull(isnull([Enterdate],[Stagedate]),[lastclosingdate])>=
                        dateadd(day,(0),'2013-04-01')) 
    then (1)  
end) PERSISTED

Please suggest me how to fix this.

What an absolutely bizarre design. Are you sure you need to have this in a persisted column? What are you actually trying to achieve? — Jens
– Jens, Commented Dec 22, 2016 at 16:22
From the msdn A computed column is a virtual column that is not physically stored in the table, unless the column is marked PERSISTED. A computed column expression can use data from other columns to calculate a value for the column to which it belongs.. — Mihail Stancescu
– Mihail Stancescu, Commented Dec 22, 2016 at 16:23
It looks like you are trying to use a persisted column where a view would be appropriate. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Dec 22, 2016 at 16:25
You don't have an Else in the Case statement. What happens if neither statement is true? That's why you are getting a non-deterministic error. — SS_DBA
– SS_DBA, Commented Dec 22, 2016 at 16:25

Alex · Accepted Answer · 2016-12-22 17:07:07Z

Your date columns make it non-deterministic. try turning them into ints like this:

[OrderIssue] AS (case when [IssueID]=(1009) AND ([Issuenumber]>=(400000) AND [Issuenumber]<=(999999) OR [Issuenumber]<(400000) AND cast(convert(varchar(8),isnull(isnull([Enterdate],[Stagedate]),[lastClosingdate]),112) as int)=(400000) OR [Issuenumber]<(700000) AND cast(convert(varchar(8),isnull(isnull([Enterdate],[Stagedate]),[lastClosingdate]),112) as int)>=20130401) then (1) end) PERSISTED)

Or you can run this other test:

if OBJECT_ID('tempdb..#tb_Test') is not null
drop table #tb_Test;

create table #tb_Test
(
IssueID int not null,
IssueNumber int not null,
Enterdate date not null,
Stagedate date not null,
lastClosingDate date not null,
[OrderIssue]  AS (
    case 
        when cast(convert(varchar(8),[Enterdate],112) as int) >= 20161222 then 0 else 1 end

) PERSISTED)

insert into #tb_Test values(1,1,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP)
insert into #tb_Test values(1,1,'20100101','20110101','20120101')

select *, cast(convert(varchar(8),[Enterdate],112) as int) from #tb_Test

cheers,

Collectives™ on Stack Overflow

Error when creating table in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related