CODEWITHSUNDEEP
sql-serverformula
semt20
semt20

Reputation: 19

How to calculate formula field to other column in SQL Server

I have a SQL Server columns with data like this:

formula    amount  ficheno
-----------------------------
100*444    100     6555
10*698     698     6555

I've already tried on SQL Server converted varchar to double getting error.

Need to get formula field to resulted field...

Upvotes: 1

Views: 539

Answers (2)

semt20
semt20

Reputation: 19

Ive followed your query and succeed Thank you so much @Andomar. Reviewed code to work on sql server<2017 with stuff function. If anyone is looking for answer ;

  declare @sql nvarchar(max) = (

 SELECT  STUFF( (SELECT ' select ' + str([LOGICALREF])  + ', ' + str([LOGICALREFI])  + ', ' + REPLACE( Formül,',','.') +';'

               from BM_211_URETIM_PLANLANAN t

               for xml path ('')

              ), 1, 1, '' )

  )

    declare @temp table ( [LOGICALREF] int, [LOGICALREFI] int, [Planlanan Miktar] FLOAT);

    insert @temp exec(@sql);

Upvotes: 0

Andomar
Andomar

Reputation: 238048

You can evaluate an expression using dynamic SQL:

declare @sql nvarchar(max) = (
    select  string_agg(cast(
            'select ' + formula + ', ' + str(amount) + ', ' + str(ficheno)
            as nvarchar(max)), ';')
    from    YourTable
);
declare @temp table (id int, amount int, ficheno int);
insert @temp exec(@sql);
select * from @temp;

-->

id      amount  ficheno
44400   100     6555
6980    698     6555

Working example at db<>fiddle.

Upvotes: 1

Related Questions