Use field data in formula - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Use field data in formula

Author

Topic

tomasjons
Starting Member

17 Posts

Posted - 2009-03-10 : 05:33:32

Hi there

I am not sure if this is actually possible, but I just wanted to try it out here in the forum.
I hope I have posted this thread on the right forum.

I have a SQL view in which I'm running some CASE WHEN 's.

I have one like this:

CASE (WHEN unitID = 1 THEN value*dproBTA/BTA) (WHEN unitID = 2 then value*BTA) END

This is running fine, but what I want to do is to put the equation (value*dproBTA/BTA) and (value*BTA) in another table and just join it in based in unitID.

I want it to look something like this:

CASE WHEN unitID = 1 THEN (equation1) WHEN unitID = 2 (equation2) END

...of course I will remove the CASE and just use the equation.

I have tried this but I get the error: "error converting datatype nvarchar to numeric.

Hope there is someone out there with a answare/solution for my problem :)

Cheers,
Tomas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-10 : 05:38:40

you need to keep them in single quotes.


CASE WHEN unitID = 1 THEN '(equation1)' WHEN unitID = 2 THEN  '(equation2)' END

keep in mind that storing this equation in table requires you to use dynamic sql for executing them.

tomasjons
Starting Member

17 Posts

Posted - 2009-03-10 : 05:50:17

Thank you for your reply.

I just tried the single quotes, and it is showing the formula, like this:

54 value*dproBTA/BTA
54 value*dproBTA/BTA

But, I want the value to be calculated based on this equation (value*dproBTA/BTA) where the values like "value", "dproBTA" and "BTA" are values/field from another table, joined in the view. Hope you understand what I meen :)

I am not so familiour with dynamic sql to exectue these. Tips?

Thank you.

Best regards,

Tomas

quote:
Originally posted by visakh16

you need to keep them in single quotes.

CASE WHEN unitID = 1 THEN '(equation1)' WHEN unitID = 2 THEN '(equation2)' END

keep in mind that storing this equation in table requires you to use dynamic sql for executing them.

Cheers,
Tomas

tomasjons
Starting Member

17 Posts

Posted - 2009-03-10 : 06:00:13

do you mean that I have to use "Stored procedure" ? I can try it..

Cheers,
Tomas

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-10 : 06:13:01

Not sure if I am understanding it correct. Why don't you post some sample data and expected output.

tomasjons
Starting Member

17 Posts

Posted - 2009-03-10 : 06:37:38

Hi

In table1 I have some records containing equations (eqID, eqEquation).

eqID(int) eqEquation(nvarvhar(50)
1 value*dproBTA/BTA
2 value/BTA
3 value

The values for the varables in the equation are stored in another (table2) wich is joined in to the my query.

All I whant to do is this:

Calculated value for row = "value*dproBTA/BTA", where I fetch the variables "value", "dproBTA" and "BTA" from another table (table2).

I have all the variables ready in the query, but I cant use the equation from table1 to calculate these.

The query is showing something like this:

54 value*dproBTA/BTA
54 value/BTA
54 value

in stead of
54 150
54 100
54 10

you see, I want the query to get the "value", "dproBTA" and "BTA".

If I just write it like this, directly in the query: SELECT dbo.tblValues.value*dbo.tblValues.dproBTA*dbo.tblValues.BTA AS value... it will work fine.

Thank you for taking your time to help me out.

This is pretty hard to explain, and I am guessing not so easy to understand :)

quote:
Originally posted by sakets_2000

Not sure if I am understanding it correct. Why don't you post some sample data and expected output.

Cheers,
Tomas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-10 : 06:40:58

thats why i told you need dynamic sql. this is not at all a good approach. can i ask why you're storing equations like this?

xpandre
Posting Yak Master

212 Posts

Posted - 2009-03-10 : 07:00:32

I guess you should consider visakh16's suggestion..

create table d1(a int, b int,c int)

create table eqn(eqnid int identity(1,1), eqn varchar(100))

insert into d1 values (1,2,3)

insert into eqn values ('a*b*c')



declare @sql nvarchar(max), @a varchar(max),@t1 varchar(max)
set @sql = 'select @t1 = eqn from eqn'
exec  SP_EXECUTESQL @sql, N'@t1 VARCHAR(max) OUTPUT', @t1 OUTPUT
set @sql = 'select ' + @t1 +' from d1'
exec  SP_EXECUTESQL @sql

tomasjons
Starting Member

17 Posts

Posted - 2009-03-10 : 07:06:36

Hi

I agree with you. I don't think that this is a good approach, but I can't figure out how to do it.

Here is what i want to do.

I have a set of records wich can have differnt units like km/s, m/s and so on. This is stored in a tableUnits:
unitID Unit EQ
1 m/s fieldValue*dproBTA/BTA
2 km/s fieldValue*dproBTA

Then a have table for field types with a reference to the unitID like this.

fieldID fieldValue unitID
77 value 1
78 value 1
79 value 2 and so on...

This was my thought, but I understand that it should not be done like this now.

In short: can I store equations and use them i calculations (where the variables are stored in another table, and will be joined in the query)

Do you have an alternative approach?

As you can see, I am new to all this, but trying to learn.

Thank you.

quote:
Originally posted by visakh16

thats why i told you need dynamic sql. this is not at all a good approach. can i ask why you're storing equations like this?

Cheers,
Tomas

tomasjons
Starting Member

17 Posts

Posted - 2009-03-10 : 07:09:30

great!! I will try this and get back to you.

Thank you all.

quote:

Originally posted by xpandre

I guess you should consider visakh16's suggestion..

create table d1(a int, b int,c int)

create table eqn(eqnid int identity(1,1), eqn varchar(100))

insert into d1 values (1,2,3)

insert into eqn values ('a*b*c')



declare @sql nvarchar(max), @a varchar(max),@t1 varchar(max)
set @sql = 'select @t1 = eqn from eqn'
exec  SP_EXECUTESQL @sql, N'@t1 VARCHAR(max) OUTPUT', @t1 OUTPUT
set @sql = 'select ' + @t1 +' from d1'
exec  SP_EXECUTESQL @sql

Cheers,
Tomas

tomasjons
Starting Member

17 Posts

Posted - 2009-03-10 : 09:34:32

Hi xpandre

This is just I was looking for !!! :) I am putting it to gether, just one more thing.

Here is the d1 tabel with eqnID supplied:

table d1 (a as int, b as int, c as int, eqnID)

How can I join the tables eqn and d1 so that each line can be calculatet individually?

Now all the rows are getting the same result, regardless of the equations in table eqn. Just the first record is beeing calulated.

Getting there..

Thanks

quote:

Originally posted by xpandre

I guess you should consider visakh16's suggestion..

create table d1(a int, b int,c int)

create table eqn(eqnid int identity(1,1), eqn varchar(100))

insert into d1 values (1,2,3)

insert into eqn values ('a*b*c')



declare @sql nvarchar(max), @a varchar(max),@t1 varchar(max)
set @sql = 'select @t1 = eqn from eqn'
exec  SP_EXECUTESQL @sql, N'@t1 VARCHAR(max) OUTPUT', @t1 OUTPUT
set @sql = 'select ' + @t1 +' from d1'
exec  SP_EXECUTESQL @sql

Cheers,
Tomas

xpandre
Posting Yak Master

212 Posts

Posted - 2009-03-10 : 09:56:00

You can change it to fit your need..

create table eqn(eqnid int identity(1,1) primary key, eqn varchar(100))

create table d1(a int, b int,c int, eqnid int foreign key  references eqn(eqnid))

insert into eqn values ('a+b+c')

insert into d1 values (1,5,6,@@identity)

insert into eqn values ('a*b*c')

insert into d1 values (1,5,6,@@identity)


declare @sql nvarchar(max), @1 int,@2 varchar(max)
declare c1 cursor for select eqnid, eqn from eqn
open c1
fetch c1 into @1, @2

while (@@fetch_status = 0)
begin

set @sql = 'select ' + @2 +'"'+@2+'" from d1 where eqnid = '+ cast(@1 as varchar)
exec  SP_EXECUTESQL @sql

fetch c1 into @1, @2
end


close c1
deallocate c1

tomasjons
Starting Member

17 Posts

Posted - 2009-03-10 : 10:47:20

Excellent xpandre, thank you for taking the time to help me out.

Now I just have to get it into one result set.
When I call this stored procedure in datagrid in asp.net, I only det the first result set.

Thanks again.

Cheers,
Tomas

tomasjons
Starting Member

17 Posts

Posted - 2009-03-10 : 11:01:35

I just put the result sets into temp_table and now I get my results out in one set...SUPER NICE :)

Cheers,
Tomas

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources