Table Design help with 2 computed columns
-
Using SQL Server 2008 r2 I am trying to design a table, which has 2 computed column based on the product of other columns including a computed column, which SQL Server doesn't like.
CREATE TABLE [dbo].[BaseRate2](
[ProductType] [varchar](100) NOT NULL,
[LinkToRate] [decimal](18, 0) NULL,
[CopyOfRates] [decimal](18, 0) NULL,
[BCdiscountLoading] [decimal](18, 0) NULL,
[OtherLoading] [decimal](18, 0) NULL,
[ClaimsLoading] [decimal](18, 0) NULL,
[NoClaimsDiscount3Yr] [decimal](18, 0) NULL,
[NoClaimsDiscount4Yr] [decimal](18, 0) NULL,
[NoClaimsDiscount5Yr] [decimal](18, 0) NULL,
[ConstructionLoading] [decimal](18, 0) NULL,
[DiscretionaryDiscount] [decimal](18, 0) NULL,
[MinPremiumLoading] [decimal](18, 0) NULL,
[SecurityDiscount] [decimal](18, 0) NULL,
[ProductLoading] AS ((((((([ClaimsLoading]*[NoClaimsDiscount3Yr])*[NoClaimsDiscount4Yr])*[NoClaimsDiscount5Yr])*[ConstructionLoading])*[DiscretionaryDiscount])*[MinPremiumLoading])*[SecurityDiscount])
) ON [PRIMARY]Now when I try to add the second computed column when uses the first computed column (ProductLoading) this is where SQL throws an error as it should do.
RateUsed as ([CopyOfRates] * [BCdiscountLoading] * [OtherLoading] * [ProductLoading])
Is there some other technique that I could design this table without manually calculating these product columns (ProductLoadings and RateUsed). Thanks Simon
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
-
Using SQL Server 2008 r2 I am trying to design a table, which has 2 computed column based on the product of other columns including a computed column, which SQL Server doesn't like.
CREATE TABLE [dbo].[BaseRate2](
[ProductType] [varchar](100) NOT NULL,
[LinkToRate] [decimal](18, 0) NULL,
[CopyOfRates] [decimal](18, 0) NULL,
[BCdiscountLoading] [decimal](18, 0) NULL,
[OtherLoading] [decimal](18, 0) NULL,
[ClaimsLoading] [decimal](18, 0) NULL,
[NoClaimsDiscount3Yr] [decimal](18, 0) NULL,
[NoClaimsDiscount4Yr] [decimal](18, 0) NULL,
[NoClaimsDiscount5Yr] [decimal](18, 0) NULL,
[ConstructionLoading] [decimal](18, 0) NULL,
[DiscretionaryDiscount] [decimal](18, 0) NULL,
[MinPremiumLoading] [decimal](18, 0) NULL,
[SecurityDiscount] [decimal](18, 0) NULL,
[ProductLoading] AS ((((((([ClaimsLoading]*[NoClaimsDiscount3Yr])*[NoClaimsDiscount4Yr])*[NoClaimsDiscount5Yr])*[ConstructionLoading])*[DiscretionaryDiscount])*[MinPremiumLoading])*[SecurityDiscount])
) ON [PRIMARY]Now when I try to add the second computed column when uses the first computed column (ProductLoading) this is where SQL throws an error as it should do.
RateUsed as ([CopyOfRates] * [BCdiscountLoading] * [OtherLoading] * [ProductLoading])
Is there some other technique that I could design this table without manually calculating these product columns (ProductLoadings and RateUsed). Thanks Simon
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
It blows, because you're using a value in your calculation that might not be available yet. Create the table, and a view - add the second computed field to the view. Alternatively, you could use a normal (non-calculated) field, and update it's contents using a trigger.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
It blows, because you're using a value in your calculation that might not be available yet. Create the table, and a view - add the second computed field to the view. Alternatively, you could use a normal (non-calculated) field, and update it's contents using a trigger.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
Eddy, Thanks for that suggestion (views) it worked, but I decided against the idea after following through on the documentation of the table / view as it made it more complex to maintain in this situation. So sadly I adopted the manual calculations before entering into the table. Thanks again :-D
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
-
Using SQL Server 2008 r2 I am trying to design a table, which has 2 computed column based on the product of other columns including a computed column, which SQL Server doesn't like.
CREATE TABLE [dbo].[BaseRate2](
[ProductType] [varchar](100) NOT NULL,
[LinkToRate] [decimal](18, 0) NULL,
[CopyOfRates] [decimal](18, 0) NULL,
[BCdiscountLoading] [decimal](18, 0) NULL,
[OtherLoading] [decimal](18, 0) NULL,
[ClaimsLoading] [decimal](18, 0) NULL,
[NoClaimsDiscount3Yr] [decimal](18, 0) NULL,
[NoClaimsDiscount4Yr] [decimal](18, 0) NULL,
[NoClaimsDiscount5Yr] [decimal](18, 0) NULL,
[ConstructionLoading] [decimal](18, 0) NULL,
[DiscretionaryDiscount] [decimal](18, 0) NULL,
[MinPremiumLoading] [decimal](18, 0) NULL,
[SecurityDiscount] [decimal](18, 0) NULL,
[ProductLoading] AS ((((((([ClaimsLoading]*[NoClaimsDiscount3Yr])*[NoClaimsDiscount4Yr])*[NoClaimsDiscount5Yr])*[ConstructionLoading])*[DiscretionaryDiscount])*[MinPremiumLoading])*[SecurityDiscount])
) ON [PRIMARY]Now when I try to add the second computed column when uses the first computed column (ProductLoading) this is where SQL throws an error as it should do.
RateUsed as ([CopyOfRates] * [BCdiscountLoading] * [OtherLoading] * [ProductLoading])
Is there some other technique that I could design this table without manually calculating these product columns (ProductLoadings and RateUsed). Thanks Simon
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
As Eddie suggested the Product loading may not exists. This is not going to make you happy. You can replace
[ProductLoading]in your second field with the ENTIRE formula from the first field. ducks and runs I told you it would not make you happy!Never underestimate the power of human stupidity RAH
-
As Eddie suggested the Product loading may not exists. This is not going to make you happy. You can replace
[ProductLoading]in your second field with the ENTIRE formula from the first field. ducks and runs I told you it would not make you happy!Never underestimate the power of human stupidity RAH
I understood the why it isn't working and was trying to see if there was another way to do this computationally. while I did consider the approach you suggested, I also have to write detailed documentation around this insurance rating API and was looking for a easy / elegant solution that would make documentation easy and clear. With the documentation in mind I decided that it would be easier for me to explain the columns and how they are calculated manually. :(
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
-
I understood the why it isn't working and was trying to see if there was another way to do this computationally. while I did consider the approach you suggested, I also have to write detailed documentation around this insurance rating API and was looking for a easy / elegant solution that would make documentation easy and clear. With the documentation in mind I decided that it would be easier for me to explain the columns and how they are calculated manually. :(
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
Simon_Whale wrote:
easy / elegant solution
Does not describe the way sql works some times.
Never underestimate the power of human stupidity RAH
-
Simon_Whale wrote:
easy / elegant solution
Does not describe the way sql works some times.
Never underestimate the power of human stupidity RAH
Hence I came here to tap into the vast knowledge of others :D
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch