Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Use a Formula from a tabl field, is it possible???

Author  Topic 

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2012-04-17 : 05:10:00
I'm not sure if this can be done and also maybe what to call it to search the net for answers.

Basically I'm looking at a system where formulas have been added into fields in a table and I need to look at the field to see what formula to use when selecting eg: eg this + this, this / this etc.

Here's a basic table I have knocked up to try different things...

CREATE TABLE #HeaderOrder(
[HeaderCode] [varchar](10) NOT NULL,
[HeaderCode2] [varchar](10) NOT NULL,
[FormulaCode] [varchar](10) NOT NULL
)
;

INSERT INTO #HeaderOrder VALUES ('ORD123','100','321+456')
;
INSERT INTO #HeaderOrder VALUES ('ORD123','200','789')
;
INSERT INTO #HeaderOrder VALUES ('ORD123','300','321/456')
;
INSERT INTO #HeaderOrder VALUES ('ORD987','400','321-456')
;
INSERT INTO #HeaderOrder VALUES ('ORD987','500','789')
;

CREATE TABLE #HeaderLine(
[HeaderLineCode] [varchar](10) NOT NULL,
[OrderValue] [decimal](38, 20) NOT NULL
)
;

INSERT INTO #HeaderLine VALUES ('321','15000')
;
INSERT INTO #HeaderLine VALUES ('456','5000')
;
INSERT INTO #HeaderLine VALUES ('789','2500')
;

-- check table OK
SELECT * FROM #HeaderOrder;
SELECT * FROM #HeaderLine;

-- These would go together as a SUM because of '321+456'
SELECT a.HeaderCode,a.HeaderCode2
FROM #HeaderOrder a
WHERE a.HeaderCode = 'ORD123'
AND a.HeaderCode2 = '100'
;
SELECT SUM(b.OrderValue) AS [OrderValue]
FROM #HeaderLine b
WHERE b.HeaderLineCode in ('321','456')
;

-- These would go together as a DIVIDE because of '321/456'
SELECT a.HeaderCode,a.HeaderCode2
FROM #HeaderOrder a
WHERE a.HeaderCode = 'ORD123'
AND a.HeaderCode2 = '300'
;
SELECT SUM(b.OrderValue) / SUM(c.OrderValue)
FROM #HeaderLine b,
#HeaderLine c
WHERE b.HeaderLineCode in ('321')
AND c.HeaderLineCode in ('456')
;

This is what the end result should be when run on the whole table.

-- End Result
CREATE TABLE #EndResultTable(
[HeaderCode] [varchar](10) NOT NULL,
[HeaderCode2] [varchar](10) NOT NULL,
[OrderValue] [decimal](38, 20) NOT NULL
)
;

INSERT INTO #EndResultTable VALUES ('ORD123','100','20000')
;
INSERT INTO #EndResultTable VALUES ('ORD123','200','250')
;
INSERT INTO #EndResultTable VALUES ('ORD123','300','3')
;
INSERT INTO #EndResultTable VALUES ('ORD987','400','10000')
;
INSERT INTO #EndResultTable VALUES ('ORD987','500','2500')
;

SELECT * FROM #EndResultTable;

-- clean up
DROP TABLE #EndResultTable;
DROP TABLE #HeaderOrder;
DROP TABLE #HeaderLine;

Any ideas or point me in the right direction or let me know if it isnt possible.

Thanks,

RC

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-17 : 08:06:49
I am not very sure about this.
Normally, when you need an entire table's data to be evaluated then you have to use only one universal condition per field that would evaluate the whole field.
But, in your case you are putting different conditions for every tuple of data. This could be possible by creating a procedure which uses a cursor based approach by selecting one tuple at a time and doing what you want.
The Set Based approach that SQL is meant to follow would not be followed here because there isn't a single condition to evaluate the Set as a whole....but different conditions for different items present in the Set.
Regarding the push, from what I understand is that you may be able to accomplish what you want(if that's the business requirement) using a procedure that uses Cursors or RBAR operations.

N 28° 33' 11.93148"
E 77° 14' 33.66384"
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-04-17 : 08:17:53
Instead of showing us this kind of approach to a solution you really shoud tell us the real problem which you would like to solve.
Sorry but this looks like it would be better to never be done this way...


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page
   

- Advertisement -