Error Validating The Formula For Column Sql Server
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up “Error validating formula” when adding Computed Column with CONVERT(DATE, column) up vote 2 down vote favorite 1 I am trying to add a computed column to a table via SSMS - I'd rather care not to get into the details on why the base column is a VARCHAR(10) and not a DATE and no, I'm not interested in changing it. When I enter the formula CONVERT(DATE, [charDob]) in the table designer SSMS reports: Error validating the the formula for column charDobTyped. Using the formula [charDob] "works" as expected, meaning the column is bound correctly. Also the formula CONVERT(DATE, '2001') "works" as expected - so there is something with using the column that is causing the error. The original column is a VARCHAR(10) and contains NULL values or values that are otherwise convertible to DATE. The query SELECT CONVERT(DATE,charDob) as dob FROM people works as expected. Also, the error message is about "invalid formula" and not about a data conversion error, so I don't believe that inconvertible is causing this. I have already saved the table with the new computed column and a dummy value and reconnected to the database - this was to ensure that there was not an odd caching issue occurring, as sometimes occurs. The error persists independent of the persisted flag - if only it were that easy to turn off. sql sql-server ssms calculated-columns share|improve this question edited Mar 29 '13 at 22:12 asked Mar 29 '13 at 21:38 user166390 Have you tried using a proper ALTER TABLE statement instead of the UI which is riddled with all kinds of bugs? Or maybe you need to clarify what you mean by try to *use* the formula? –Aaron Bertrand Mar 29 '13 at 21:41 1 @AaronBertrand Ding ding ding. Point goes to you - yes, it does work correctly when doing it
the formula for a computed column in SQL Server 2005 using SQL Server Management Studio 2008. I get the error validating formula and I'm not sure how to write the formula to work. If it were standard math the formula would be: (mmtot * 22) + (mattot * 2) + odctot mmtot, mattot, and odctot are also computed columns so that may be the problem but I'm not sure. computed-columns mssql DAmanding 2 posts since May 2010 Newbie Member 2Contributors 3Replies 4Views 6 YearsDiscussion Span 6 Years Ago Last Post by tesuji 0 tesuji 135 6 Years Ago Hi, you should give some more details, especially on the obviously wrong select statement containing those computed columns. If mmtot, mattot, and odctot http://stackoverflow.com/questions/15711807/error-validating-formula-when-adding-computed-column-with-convertdate-column are also computed columns, what they are computed from, from real table columns? Simple post your select statement and some information on the involved tables too. -- As for example select (a+ b) * c as x, x + d / e as y, x + y as z from t; -- where a,b,c,d,e are columns of t and x , y, z are computed columns -- is valid on almost every database systems, I have just https://www.daniweb.com/programming/databases/threads/295290/error-validating-formula-for-computed-column run this on ms sql server 2008. -- tesu 0 Discussion Starter DAmanding 6 Years Ago I genuinely appreciate your response. Because these are computed columns there is no sql statement involved. I have since learned that you cannot compute a column based on other computed columns. :( For others who may stumble across this thread, here is some basic information on using computed columns at this website: http://www.mssqltips.com/tip.asp?tip=1682 The example given shows using sql for all the work in creating them, but if you have sql server management studio you can just go into the design properties of a table (right click on table), click on the field type and put your formula in the computed columns section. 0 tesuji 135 6 Years Ago Hello DAmanding One live and learn! I have fiddled about with plenty of computed columns and finally I got this monster solely containing computed columns: select 4 as "need", 5 as drink, 2 as "of", 6 as "nature", 3 as "now", 1 as "I", 1 as "a", 9 as "alcoholic", cast( 8915./62991.+3 as varchar) as "Wilbur", '<'as '', cast( "now"||'.'||"I"||"need" ||"a"||"drink"||"alcoholic"|| "of"||"nature" as varchar) as "Now I need a drink alcoholic of nature", '<' as ' ', cast(9552./67441.+3 as varchar) as Knorr, cast(WilBur+cast(Knorr as double) -2*"Now I need a drink alcoholic of nature" as varchar) as "Chin-chin!"; which seems to be a true cracker for
SQL Server experts to answer whatever question you can come up with. Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums. SQL http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=77715 Server Forums Profile | ActiveTopics | Members | Search | ForumFAQ Register Now and get http://www.justskins.com/forums/error-validating-formula-163548.html your question answered! Username: Password: Save Password Forgot your Password? All Forums General SQL Server Forums New to SQL Server Programming Formula for Computed Column Reply to Topic Printer Friendly Author Topic DianeJ Starting Member New Zealand 6 Posts Posted-01/17/2007: 18:16:56 I have downloaded the SQL Server Books online and found the section on Computed columns. In my error validating small banking program I have columns named Deposit/Withdrawel and Balance. If I am reading this right I need to set the Computed Column Specification of my Balance Column in order to perform the calculation. My problem now is that I have no idea how to word the formula and other than telling me what a formula is the SQL Server Books online is no help whatsoever, so any help would be awesome.If i've misunderstood what I error validating the am meant to do somebody please please tell me. Thanks SwePeso Patron Saint of Lost Yaks Sweden 30421 Posts Posted-01/17/2007: 18:24:52 ALTER TABLE
trying to populate a column of a table by using the a formula that calls the following function where code_racc is a value of another column in the same table. 1.If I execute this function from the Query yzer i donot get any problem when i do insert in the formula the code dbo.[VCAMPO_DARIO] (code_racc) I get an error in the validation of the formula. 2. If i do insert in the formula the code dbo. [VCAMPO_DARIO] (code_racc) i do not get an error only if I substitute SET STR_OBBL=CAST(dbo.code_racc_12(CODE_RACC) AS CHAR (12)) with SET STR_OBBL='9'+CODE_RACC CREATE FUNCTION ... Thread Tools Show Printable Version Email this Page… Subscribe to this Thread… Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode July 15th,06:36 PM #1 Error validating Formula Hallo, I'm trying to populate a column of a table by using the a formula that calls the following function where code_racc is a value of another column in the same table. 1.If I execute this function from the Query yzer i donot get any problem when i do insert in the formula the code dbo.[VCAMPO_DARIO] (code_racc) I get an error in the validation of the formula. 2. If i do insert in the formula the code dbo. [VCAMPO_DARIO] (code_racc) i do not get an error only if I substitute SET STR_OBBL=CAST(dbo.code_racc_12(CODE_RACC) AS CHAR (12)) with SET STR_OBBL='9'+CODE_RACC CREATE FUNCTION [VCAMPO_DARIO] ( CODE_RACC CHAR(11) ) RETURNS CHAR(18) AS BEGIN DECLARE INT_OBBL BIGINT DECLARE OBBL CHAR(16) DECLARE RESTO BIGINT DECLARE STR_OBBL CHAR(12) SET STR_OBBL=CAST(dbo.code_racc_12(CODE_RACC) AS CHAR (12)) --SET STR_OBBL='9'+CODE_RACC SET OBBL = TE('0',16-LEN(RTRIM(STR_OBBL))) + RTRIM(STR_OBBL) SET INT_OBBL=abs(CONVERT(BIGINT,OBBL)) SET RESTO=INT_OBBL%93 RETURN OBBL + TE('0',2-LEN(RTRIM(CAST(RESTO AS VARCHAR(2))))) + RTRIM(CAST(RESTO AS VARCHAR(2))) END Am I doing something wrong? Dario Guest « ADODB Cmd with DSN | Disconnect users help. » Similar Threads Question Formula A