Showing posts with label surface. Show all posts
Showing posts with label surface. Show all posts

Tuesday, March 20, 2012

Problem with formula input with SQL Server Management Studio Express

Hi all,

I'm creating a database using SQL Server Management Studio Express and have a problem. I've got 4 columns: Surface, Rent, MonthlyIncome and AnnualIncome. Surface and Rent are inputed by user, MonthlyIncome is straight calculation Rent*Surface using Computed Column Formula. But, when I want to calculate AnnualIncome SQL SMSE doesn't allow me to input formula like this MonthlyIncome*12. Where I can read about limitations in formula field?

TIA.

Przemek

hi,

computed columns can not include other computed columns in their definition..

http://msdn2.microsoft.com/en-us/library/ms191250.aspx

but, as the definition of your AnnualIncom column is not that heavy ( ) you can define it on the base columns without problems as

SET NOCOUNT ON;

USE tempdb;

GO

CREATE TABLE dbo.t1 (

Id int NOT NULL PRIMARY KEY,

Surface int NOT NULL DEFAULT 0,

Rent decimal(18,4) NOT NULL DEFAULT 0,

MonthlyIncome AS (Surface * Rent),

AnnualIncome AS ((Surface * Rent) * 12 )

);

GO

INSERT INTO dbo.t1 VALUES ( 1 , 10, 1.8 );

SELECT * FROM dbo.t1;

GO

DROP TABLE dbo.t1;

--<-

Id Surface Rent MonthlyIncome AnnualIncome

-- -- --

1 10 1.8000 18.0000 216.0000

regards|||Hi Andrea, thank you very much for help. I shoul read msdn more carefully.

Przemek