August 18, 2008 at 6:58 am
create table mytable (fee1 money, fee2 money, fee3 money)
insert into mytable values (32, 11, 24)
insert into mytable values (12, 25, 44)
insert into mytable values (16, 14, 42)
Thanks everyone who helped me with the previous question!
Now, I have another task to do - I need to summarize values of all the 'fee' fields for each row, like:
fee1+fee2+fee3.
Now there three 'fee' fields, but since I don't know how many of them could be there in the future, I need to use dynamic SQL to do that.
Can't figure out how.
Could you please help?
August 18, 2008 at 7:12 am
You'd have to query information_schema.columns for the number of columns and then probably build a loop to sum up the values.
Note that storing an variable number of fees in the table sounds like poor database design. Are these separate types of fees? Perhaps a computed column that does this for you for each row?
August 18, 2008 at 7:16 am
Steve,
Unfortunately, I cannot do anything about the database design here.
Yes, these are all different types of fees and they each are supplied with 'feetype' columns.
I've tried information_schema.columns before, it didn't work for some reason. Perhaps because I use linked servers - SQL2000 and SQL2005.
Thank you anyway!
August 18, 2008 at 7:50 am
The table with 'fee' columns is on SQL2000.
I'm going to have to execute my script from SQL2005.
When I refer information_schema.columns from the 2000 server, it works fine.
When I try running the query from the 2005 server, it gives me an error:
select * from [server_2000].dbst.information_schema.columns
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "server_2000" does not contain the table ""dbst"."information_schema"."columns"". The table either does not exist or the current user does not have permissions on that table.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply