Error validating the formula for column sql server

Rated 3.95/5 based on 763 customer reviews

UDF_Get Leave Balance(Emp Numb)) ) GO --Insert sample data INSERT INTO CCTest (Emp Numb, Designation) SELECT 840, 'DBA' UNION ALL SELECT 805, 'DBA' UNION ALL SELECT 846, 'Manager' UNION ALL SELECT 88, 'Manager' UNION ALL SELECT 90, 'Developer' GO -- Check the result SELECT * from CCTest GO So below we can see the number of remaing days available for each employee.There are some considerations related to UDFs when a computed column is to be used in an index.I have defined party_group as formula column in my table contract_mst.When I modify the table and save, the sql server 2000 gives error, it says "Error validating the formula for column [party_group]" However when I paste same fromula in the query, there isn't any error. SELECT CONTRACT_NO, PARTY_GROUP= (left(replace([contract_no')) - 1))) FROM CONTRACT_MST STPL/AC/014/10/04 AC STPL/AD/037/07/04 AD STPL/AHV/002/08/04 AHV STPL/AIL/016/12/04 AIL STPL/AIL/141/08/04 AIL STPL/AKLQ/004/09/04 AKLQ STPL/ALMR/002/12/04 ALMR STPL/ALMR/111/09/04 ALMR STPL/ALTF/155/08/04 ALTF STPL/AM/036/12/04 AM STPL/AMN/105/11/04 AMN STPL/ANJ/006/08/04 ANJ STPL/ANJ/053/10/04 ANJ STPL/APTQ/005/09/04 APTQ STPL/APTQ/114/08/04 APTQ STPL/ARI/004/11/04 ARI STPL/ASH/019/10/04 ASH STPL/ASS/131/08/04 ASS STPL/ATR/052/12/04 ATR STPL/ATS/087/07/04 ATS STPL/AYB/001/09/04 AYB STPL/BG/008/09/04 BG Hi Mateen, Are you trying to do this in Enterprise Manager ??We have a new requirement to set the age limit to 65 for managers, but keep it at 60 for all other employees.We can accomplish this by using a conditional formula using a CASE statement as shown in the following code.In such cases we run the risk of having an error because of a divide by zero.

--Script # 3: Use UDF to access column in other table -- Use sample database USE [Adventure Works] GO -- Create Table to reference in UDF IF OBJECT_ID('Leave Balance', 'U') IS NOT NULL DROP TABLE Leave Balance GO CREATE TABLE [dbo].[Leave Balance] ( [Emp Numb] INT NOT NULL, [Leaves Availed] TINYINT NOT NULL, ) GO --Insert sample data INSERT INTO Leave Balance SELECT 840, 12 UNION ALL SELECT 805, 6 UNION ALL SELECT 846, 13 UNION ALL SELECT 88, 7 UNION ALL SELECT 90, 15 GO -- Create UDF to get leave balance IF OBJECT_ID('UDF_Get Leave Balance', 'FN') IS NOT NULL DROP FUNCTION UDF_Get Leave Balance GO -- Create UDF to use in computed column CREATE FUNCTION UDF_Get Leave Balance (@Emp Numb int) RETURNS TINYINT AS BEGIN DECLARE @Leave Balance TINYINT SELECT @Leave Balance = (20 - Leaves Availed) FROM Leave Balance WHERE Emp Numb = @empnumb RETURN @Leave Balance END GO -- Create Table to use computed column IF OBJECT_ID('CCTest', 'U') IS NOT NULL DROP TABLE CCtest GO CREATE TABLE [dbo].[CCtest] ( [Emp Numb] INT NOT NULL, [Designation] VARCHAR(50) NOT NULL, [Leave Balance] AS ([dbo].Computed columns can add great flexibility in database design.It is possible to conditionally apply the computational expression, handling divided by zero error and accessing any column outside the table of computed column.The fourth record we insert into the table would cause a divide by zero issue, but the NULLIF function converts this to a NULL.-- Script# 2: Avoiding divided by zero error -- Use sample database USE [Adventure Works] GO -- Create Table with computed column IF OBJECT_ID('CCtest', 'U') IS NOT NULL DROP TABLE CCtest GO CREATE TABLE [dbo].[CCtest] ( [Numerator] int NOT NULL, [Denominator] int NOT NULL, [Result] AS (Numerator/NULLIF(Denominator,0)) ) GO --Insert sample data INSERT INTO CCTest (Numerator, Denominator) SELECT 840, 12 UNION ALL SELECT 805, 6 UNION ALL SELECT 846, 3 UNION ALL SELECT 88, 0 UNION ALL SELECT 90, 15 GO -- Check the result SELECT * from CCTest GO Since you can not divide by NULL the returned value is NULL for this column for this one record.

Leave a Reply