Solved

Executing SQL code within a table field

Posted on 2011-02-23
10
416 Views
Last Modified: 2012-05-11
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_GreaterUDF(125*.0001,.0625) + 5.5*.01)

Note: CommonPrograms.dbo.cp_GreaterUDF is a user-defined scalar function that selects the greater of two values.

Thanks for the help!

Lenny

How can I do this?
0
Comment
Question by:LennyGray
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 34964162
Create your table with DEFAULT clause on TheFinalForumal column if possible.
CREATE TABLE your_table ( 
              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    DEFAULT (dbo.CP_GREATERUDF(125 * .0001,.0625) + 5.5 * .01), 
         CalculationResults DECIMAL(10,5)    NULL)

Open in new window

0
 
LVL 26

Accepted Solution

by:
tigin44 earned 500 total points
ID: 34964261
try this where AAA is the tablename of your table





declare @sql   varchar(255);
DECLARE @AccountNumber varchar(50);

DECLARE cOrder CURSOR FOR
SELECT AccountNumber 
FROM AAA;
OPEN cOrder;
FETCH NEXT FROM cOrder INTO @AccountNumber
WHILE @@FETCH_STATUS = 0
BEGIN
	SELECT @sql =  'UPDATE T ' +
					'SET T.CalculationResults = (' + TheFinalFormula + ') ' +
					'FROM AAA T ' +
					'WHERE AccountNumber = ' + @AccountNumber
	FROM AAA
	WHERE AccountNumber = @AccountNumber

	EXEC (@sql)				
	FETCH NEXT FROM cOrder INTO @AccountNumber
END

Open in new window

0
 
LVL 10

Author Comment

by:LennyGray
ID: 34964282
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_GreaterUDF(125*.0001,.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

0
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
LVL 10

Author Comment

by:LennyGray
ID: 34964338
Thanks, tigin44.

I am getting an error

"Msg 207, Level 16, State 1, Line 1
Invalid column name 'USD_BC1C7_00003536_1CSLIBOR_O'  "

What does this mean?

Lenny
0
 
LVL 10

Author Comment

by:LennyGray
ID: 34964351
Account number is not unique, by the way.
0
 
LVL 26

Expert Comment

by:tigin44
ID: 34964367
its probably that some of the formulas referencing a column named 'USD_BC1C7_00003536_1CSLIBOR_O' which is not in your table... check the formula column...
0
 
LVL 26

Expert Comment

by:tigin44
ID: 34964398
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 ..
0
 
LVL 10

Author Comment

by:LennyGray
ID: 34964601
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.FortranTable
table that has the formula with the following code:

INSERT INTO   MajorGroups.dbo.FortranTable
SELECT   AccountNumber,
              QRMYieldCIT,
              @TheTestResults,
              Floor_Pricing_Txt_Formula,
              'SELECT(' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Floor_Pricing_Txt_Formula, 'PRIME', @ThePrime), 'HigherOf', 'CommonPrograms.dbo.cp_GreaterUDF'), '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.
0
 
LVL 26

Expert Comment

by:tigin44
ID: 34964708

ALTER TABLE dbo.AAA ADD
      IDcolumn int NOT NULL IDENTITY (1, 1)
0
 
LVL 10

Author Closing Comment

by:LennyGray
ID: 34964898
Brilliant work!!!

Thanks!
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question