LennyGray
asked on
Executing SQL code within a table field
Hi All -
I have a SqlServer table that has this structure:
AccountNumber VARCHAR(50) NULL,
QRMYieldCIT DECIMAL(10,5) NULL,
TestResults DECIMAL(10,5) NULL,
Floor_Pricing_Txt_Formula VARCHAR(250) NULL,
TheFinalFormula VARCHAR(255) NULL,
CalculationResults DECIMAL(10,5) NULL
In the table field, TheFinalFormula, there contains a statement that I would like to execute within a query/stored procedure that will fill the result of the formula into the table field, CalculationResults.
Each data record has its own formula in TheFinalFormula and the formula is not generally duplicated.
Here is a sample statement that would appear in the table field, TheFinalFormula:
SELECT(CommonPrograms.dbo. cp_Greater UDF(125*.0 001,.0625) + 5.5*.01)
Note: CommonPrograms.dbo.cp_Grea terUDF is a user-defined scalar function that selects the greater of two values.
Thanks for the help!
Lenny
How can I do this?
I have a SqlServer table that has this structure:
AccountNumber VARCHAR(50) NULL,
QRMYieldCIT DECIMAL(10,5) NULL,
TestResults DECIMAL(10,5) NULL,
Floor_Pricing_Txt_Formula VARCHAR(250) NULL,
TheFinalFormula VARCHAR(255) NULL,
CalculationResults DECIMAL(10,5) NULL
In the table field, TheFinalFormula, there contains a statement that I would like to execute within a query/stored procedure that will fill the result of the formula into the table field, CalculationResults.
Each data record has its own formula in TheFinalFormula and the formula is not generally duplicated.
Here is a sample statement that would appear in the table field, TheFinalFormula:
SELECT(CommonPrograms.dbo.
Note: CommonPrograms.dbo.cp_Grea
Thanks for the help!
Lenny
How can I do this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the quick response.
I am looking for the table field, CalculationResults, to be filled with the result of the formula that is in the table field, TheFinalFormula. Each data record (row) would have a formula and then a calculted result of the formula.
For example, the formula, SELECT(CommonPrograms.dbo. cp_Greater UDF(125*.0 001,.0625) + 5.5*.01), that is in the table field, TheFinalFormula, would yield a result in CalculationResults that would be equal to 2.0525000000.
I hope that this explains what I am attempting.
Lenny
I am looking for the table field, CalculationResults, to be filled with the result of the formula that is in the table field, TheFinalFormula. Each data record (row) would have a formula and then a calculted result of the formula.
For example, the formula, SELECT(CommonPrograms.dbo.
I hope that this explains what I am attempting.
Lenny
ASKER
Thanks, tigin44.
I am getting an error
"Msg 207, Level 16, State 1, Line 1
Invalid column name 'USD_BC1C7_00003536_1CSLIB OR_O' "
What does this mean?
Lenny
I am getting an error
"Msg 207, Level 16, State 1, Line 1
Invalid column name 'USD_BC1C7_00003536_1CSLIB
What does this mean?
Lenny
ASKER
Account number is not unique, by the way.
its probably that some of the formulas referencing a column named 'USD_BC1C7_00003536_1CSLIB OR_O' which is not in your table... check the formula column...
if account number is not uniquw than this will not work... you may change AccountNumber with a unique column name or add a new column with a unique value to get this code to work ..
ASKER
What is the SQL equivalent to the MsAccess "autonumber"?
I could add a field to the table named TheIndex before I use your process, I tried uniqueidentifier and it did not automatically insert a unique number.
I am creating the MajorGroups.dbo.FortranTab le
table that has the formula with the following code:
INSERT INTO MajorGroups.dbo.FortranTab le
SELECT AccountNumber,
QRMYieldCIT,
@TheTestResults,
Floor_Pricing_Txt_Formula,
'SELECT(' + REPLACE(REPLACE(REPLACE(RE PLACE(REPL ACE(REPLAC E(REPLACE( Floor_Pric ing_Txt_Fo rmula, 'PRIME', @ThePrime), 'HigherOf', 'CommonPrograms.dbo.cp_Gre aterUDF'), 'BP', '*.0001'), 'F3LIB', @TheF3LIB), 'LIBO', @TheLIBO), 'Spread(', ''), '%)', '*.01') + ')',
NULL
FROM InputHistory
I appreciate your help since I am just learning Sql coding after using Vb and Access for the past 15 years.
Thanks again.
I could add a field to the table named TheIndex before I use your process, I tried uniqueidentifier and it did not automatically insert a unique number.
I am creating the MajorGroups.dbo.FortranTab
table that has the formula with the following code:
INSERT INTO MajorGroups.dbo.FortranTab
SELECT AccountNumber,
QRMYieldCIT,
@TheTestResults,
Floor_Pricing_Txt_Formula,
'SELECT(' + REPLACE(REPLACE(REPLACE(RE
NULL
FROM InputHistory
I appreciate your help since I am just learning Sql coding after using Vb and Access for the past 15 years.
Thanks again.
ALTER TABLE dbo.AAA ADD
IDcolumn int NOT NULL IDENTITY (1, 1)
ASKER
Brilliant work!!!
Thanks!
Thanks!
Open in new window