locked
Dynamic query calculate column RRS feed

  • Question

  • User1126057398 posted

    I had created a dynamic query in which I have to multiply the table column with variable, but it's giving error 'Arithmetic overflow error converting varchar to data type numeric.':. How can this be achieved?

    SET @Sql = ';WITH x AS
    (
    SELECT MET_ACTIVITY_DESCRIPTION, num, sn
    FROM (
    SELECT MET_ACTIVITY_DESCRIPTION,
    [Exercise Category] = CONVERT(varchar(32), Category),
    [MET VALUE] = CONVERT(varchar(32), MET_VALUE),
    [Calories Burn/Hour] = CONVERT(varchar(32), (MET_VALUE*' + @weight*1.0 + '))
    FROM DIET_MET_DETAIL_MST A LEFT OUTER JOIN DIET_MET_CAT_MST B on A.Category_Code=B.Code
    ) AS d
    UNPIVOT (num FOR sn IN
    ([Exercise Category], [MET VALUE], [Calories Burn/Hour])
    ) AS unp
    )
    SELECT MET_ACTIVITY_DESCRIPTION = sn,' + @ActivityToCompare + ' FROM x
    PIVOT (MAX(num) FOR MET_ACTIVITY_DESCRIPTION IN (' + @ActivityToCompare + ')) AS p order by MET_ACTIVITY_DESCRIPTION;'

    Friday, April 5, 2019 10:34 AM

Answers

  • User1126057398 posted

    Solved the issue.Changed the above line to:

    [Calories Burn/Hour] = CONVERT(varchar(32), MET_VALUE*'+ convert(varchar,@weight) + ')

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 5, 2019 11:31 AM