|
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
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
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
|
|
|
|
|
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
|
|
|
|
|
Hence I came here to tap into the vast knowledge of others
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
|
|
|
|
|
How to use pivot in sql server here am having 5 colleges in that 5 programs runnig. in that one college runnig 5 programs and another college runnig 3 programs, another college running 2 programs. when am using pivot in sql server each college displaying 5 programs but i want one college runnig 5 programs and another college runnig 3 programs, another college running 2 programs like this. how to use only one select command
|
|
|
|
|
Documentation is here[^]. What have you tried?
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
|
|
hi
i wanna update some rows in files table but i face with error.
can every one help me?
my code is :
UPDATE [Common]..[com_Files] SET
[ReferenceGuid] = (SELECT [Guid] FROM [Personnel]..Missions
WHERE [PersonnelSystem]..Missions.ID= [Common]..[com_Files].ReferenceID)
WHERE
[ReferenceFlag] = 72 AND
[ReferenceGuid] IS NULL AND
[ReferenceSoftwareGuid] = '66944D9C-31F4-4594-9407-4E1F64C079D9' and
[ReferenceID]= [PersonnelSystem]..Missions.ID
GO
thanks for any help
|
|
|
|
|
mehdi.sabet wrote: [Common]..[com_Files]
mehdi.sabet wrote: [PersonnelSystem]..Missions.ID
mehdi.sabet wrote: [Common]..[com_Files].ReferenceID
Why two dots? Does not look correct.
|
|
|
|
|
this is ok in my frame work
and you use this query with one dot
my problem is in join but not in dots
thank you for reply
|
|
|
|
|
You forget to include the error, and a hint as to which DBMS you're using.
Assuming MS SQL, try:
UPDATE
F
SET
[ReferenceGuid] = M.[Guid]
FROM
[Common]..[com_Files] As F
INNER JOIN [PersonnelSystem]..Missions As M
ON F.ReferenceID = M.ID
WHERE
F.[ReferenceFlag] = 72
AND
F.[ReferenceGuid] Is Null
AND
F.[ReferenceSoftwareGuid] = '66944D9C-31F4-4594-9407-4E1F64C079D9'
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
ok thanks for reply
i solved this problem with this code:
UPDATE mycompany.dbo.Files
SET [ReferenceGuid] = ( SELECT mission.[GUID]
FROM dbo.Mission mission
WHERE mission.ID = [ReferenceID]
)
WHERE [ReferenceSoftwareGuid] = '66944D9C-31F4-4594-9407-4E1F64C079D9'
thanks anyway
|
|
|
|
|
in my database Table i have a column name BookDate(nvarchar) 22/03/2013 01:25 PM stored data in this format
now i need to match only with date out of date time it should be matched with current date please help with a query
Thanking you
|
|
|
|
|
nukalarajesh wrote: BookDate(nvarchar)
Don't store dates as strings. Change the column to datetime (or datetime2(0) if you're using SQL 2008 or higher), and then query for BookDate >= @Today And BookDate < @Tomorrow .
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
If you are wondering why your other post was removed (in less than 10 minutes!), it was because of the "urgency". We're volunteers, answering questions in our spare time. Had you included it with your question, then the question would have been gone too.
If you want an answer "fast", then you better make sure that there's a clear problem description, and some example-code showing what you tried to achieve. You might also want to include a subject-line that's a bit more descriptive; posts in the database-forum usually have some SQL, and it doesn't give a clue of what you're doing.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Just in case you did not take Richard seriously - DO NOT STORE DATES AS TEXT, USE THE DATETIME DATATYPE. This is the basis of your problem.
How do I make that red, can I make it flash, can I make it jump through the monitor and thump it into the desk?
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
use trunc(your_field name)= trunc(sysdate) or sysdate-1 for yesterday.
or trunc(field_name)='12-apr-2012'
|
|
|
|
|
i'm accessing this .sql file from a .ksh(script) file.
error-subprogram or cursor reference out of scope
{code}
declare
CURSOR cur AS select a from abc;
begin
for i in cur loop
dbms.putline('recored is' rec.recor_num);
end loop
|
|
|
|
|
What database is this for as it doesn't look like a SQL Server cursor
TSQL Cursor[^]
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 looks like Oracle PL/SQL to me(I used this 7+ months ago so I could be wrong...)
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Not used Oracle for a while but I would have a read of this Stackoverflow: pl-sql cursor[^]
it shows how you would construct your cursor. I think your problem is here
GugliMugli wrote: CURSOR cur AS select a from abc;
it should be
declare cursor cur is
select a from abc
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
|
|
|
|