COMPUTED COLUMN ISSUE - 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)

COMPUTED COLUMN ISSUE

Author

Topic

miranwar
Posting Yak Master

125 Posts

Posted - 2010-12-29 : 09:56:47

Hello ,

I am trying to create a computed column on a table using a HASHBYTE Expression for all non computed columns in the table. Code is attached below. When I try to make the column PERSISTED I get an error saying column is non deterministic.

alter table Journal add cs_row2 as
HASHBYTES ('SHA1',CAST(ISNULL([Journal_SID],'') AS NVARCHAR(255)) +CAST(ISNULL([Entry_Date],'') AS NVARCHAR(255)) +CAST(ISNULL([Additional_Ref],'') AS NVARCHAR(255)) +CAST(ISNULL([Narrative],'') AS NVARCHAR(255)) +CAST(ISNULL([Source_Table],'') AS NVARCHAR(255)) +CAST(ISNULL([Source_ID],'') AS NVARCHAR(255)) +CAST(ISNULL([Message_Ref],'') AS NVARCHAR(255)) +CAST(ISNULL([Original_Message_Ref],'') AS NVARCHAR(255)) +CAST(ISNULL([Journal_Status_Code],'') AS NVARCHAR(255)) +CAST(ISNULL([Settlement_Method_Code],'') AS NVARCHAR(255)) +CAST(ISNULL([Journal_Instantiating_Action_Code],'') AS NVARCHAR(255)) +CAST(ISNULL([Claim_Line_SID],'') AS NVARCHAR(255)) +CAST(ISNULL([Claim_Movement_SID],'') AS NVARCHAR(255)) +CAST(ISNULL([Insured_Line_SID],'') AS NVARCHAR(255)) +CAST(ISNULL([Risk_Section_SID],'') AS NVARCHAR(255)) +CAST(ISNULL([Posted_By_User_SID],'') AS NVARCHAR(255)) +CAST(ISNULL([Accounting_Period_Code],'') AS NVARCHAR(255)) +CAST(ISNULL([Insured_Party_SID],'') AS NVARCHAR(255)) +CAST(ISNULL([Payee_Payer_Party_SID],'') AS NVARCHAR(255)) +CAST(ISNULL([Broker_Party_SID],'') AS NVARCHAR(255)) +CAST(ISNULL([Journal_Group_SID],'') AS NVARCHAR(255)) +CAST(ISNULL([Ledger_ID],'') AS NVARCHAR(255)) +CAST(ISNULL([Journal_Type_Code],'') AS NVARCHAR(255)) +CAST(ISNULL([Replace_Of_Journal_SID],'') AS NVARCHAR(255)) +CAST(ISNULL([Contra_Journal_SID],'') AS NVARCHAR(255)) +CAST(ISNULL([Contra_Of_Journal_SID],'') AS NVARCHAR(255)) +CAST(ISNULL([jouInsDate],'') AS NVARCHAR(255)) +CAST(ISNULL([jouInsSIDusr],'') AS NVARCHAR(255)) +CAST(ISNULL([jouModVersn],'') AS NVARCHAR(255)) +CAST(ISNULL([jouUpdDate],'') AS NVARCHAR(255)) +CAST(ISNULL([jouUserLastUpdSIDusr],'') AS NVARCHAR(255)) +CAST(ISNULL([Functional_Amount_Total],'') AS NVARCHAR(255)) +CAST(ISNULL([bChanged],'') AS NVARCHAR(255)) +CAST(ISNULL([dtLoadDate],'') AS NVARCHAR(255)) +CAST(ISNULL([iLoadNumber],'') AS NVARCHAR(255)) ) PERSISTED

Secondly I am able to create the computed column without it being persisted . However, when I query the table as
" select * from journal" I get an error message Error converting data type varchar to numeric.

Thanks in Advance for any assistance

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-29 : 20:55:02

This is probably the cause of the convertion error
declare @i numeric
select cast(isnull(@i,'') as char)
gives error
try
declare @i numeric
select cast(isnull(@i,0) as char)
declare @i numeric
select isnull(cast(@i as char),'')

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-29 : 21:07:27

And ry it on a small test table. I think you'll find that hashbytes is non-deterministic from your result.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

miranwar
Posting Yak Master

125 Posts

Posted - 2011-01-05 : 16:49:13

Thank You Issue Fixed

quote:
Originally posted by nigelrivett

And ry it on a small test table. I think you'll find that hashbytes is non-deterministic from your result.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources