July 7, 2008 at 9:47 pm
sum(isnull('Premium' + cast(@i as varchar),0))
There are fields in the table - 'Premium1', 'Premium2', etc., of 'money' datatype.
The number of those fields can get changed by the user who sends us the source data. That's why we decided to loop through the fields with the names beginning with 'Premium'.
@i is of an 'int' datatype and is the '1','2', '3', etc. part of the field name.
I need to summarize values of each field separately.
This is the error I get on the execution of the above statement:
Msg 8117, Level 16, State 1, Line 52
Operand data type varchar is invalid for sum operator.
How do I fix it?
Thanks in advance!
July 7, 2008 at 10:08 pm
Hi. Could you post all of your code? It sounds like you are trying to perform a summation with certain columns, dynamically. Something that should probably happen with dynamic t-sql.
Thanks
July 7, 2008 at 10:14 pm
You cannot trick sql into thinking that is a column name. You would have to use some form of dynamic sql and then execute it.
example.
declare @sql varchar(8000)
Set @sql = 'Select sum(premium ' + cast(@i as varchar(10))+ ') From sometable'
exec(@sql)
You may also want to look into the difference between exec and sp_executesql. I am just using exec for sample purposes.
July 9, 2008 at 7:44 am
What format is the source data? Is there a limit to the number of columns you will need to query? There are going to be several ways that you can resolve this (with most using Dynamic SQL as the gentleman above suggest), but depending on your objective, environment, one solution will work better for you than another.
July 9, 2008 at 10:43 am
HI,
Please post code and the same data, so that we can debug the problem. It looks there is some issue with datatype, wheer you are getting varchar and you are trying to do SUM on that.
Thanks -- VJ
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply