Casting and Sum Error

Question

I get an error message that states data loss might occur when casting from decimal(9,1) to decimal(7,4). This error occurs on the sum(b.Column2...) line.

    insert into @tempTable
    select a.Column1, sum(b.Column2 * c.Column3 * d.Column4)
    from dbo.Table1 as a
    join dbo.Table2 as b on specified columns
    join dbo.Table3 as c on specified columns
    join dbo.Table4 as d on specified columns

I have tried the following:

    insert into @tempTable
    select a.Column1, cast(sum(b.Column2 * c.Column3 * d.Column4) as decimal (7,4))
    from dbo.Table1 as a
    join dbo.Table2 as b on specified columns
    join dbo.Table3 as c on specified columns
    join dbo.Table4 as d on specified columns

I have also tried:

   declare @tempColumn2 decimal(7,4);

   select @tempColumn2 = cast(a.Column1 as decimal(7,4)) -- This gives me an unresolved error message.

    insert into @tempTable
    select a.Column1, sum(@tempColumn2 * c.Column3 * d.Column4)
    from dbo.Table1 as a
    join dbo.Table2 as b on specified columns
    join dbo.Table3 as c on specified columns
    join dbo.Table4 as d on specified columns

Any tips would be appreciated! Thanks!

Answer 1

You are getting an error message because you are attempting to squeeze the range {-999999999.9 .. 999999999.9} into {-999.9999 .. 999.9999}

The answer to "How do I fit decimal(9,1) into decimal(7,4)?" is: you don't.