Formula within a Field

Asked

Viewed 186 times

3

In a field I have the value of a formula ((B/2)+ C), where the value B and C comes from other fields, how can I exchange the values of the letters for their real values and calculate the formula?

I just need a north on how to do this, 'cause I can’t find a pattern.

Note: There are n types of formulae, the value is not fixed.

EDIT

inserir a descrição da imagem aqui

I only do a search in this simple and precise table you replace the value of B and C by their values. Then calculate the formula.

Ex: (10/2) + 5
sql sql-server

  • You could further exemplify your question with return examples of your query and how is your table too?

  • I edited, my select is only to fetch the values, but my problem is how to replace these values and then calculate the entire formula.

  • Vide SQL Server Eval | Dynamically evaluate arithmetic operation and expression in SQL: https://github.com/zzzprojects/Eval-SQL.NET/wiki/SQL-Server-Eval-%7C-Dynamically-evaluate-arithmetic-Operation-and-Expression-in-SQL

1 answer

3


I set an example using the exec

SET NOCOUNT ON;
--crio uma tabela temporária para simular a sua tabela
CREATE TABLE #formulas_table(
formula varchar(100),
A int, 
B int,
C int)
--insiro uns dados de amostra
insert into #formulas_table
select '(B/2)+ C' formula, 10 B,5 C,0 A
union all select '(B/1)+ C' formula, 10 B,5 C,0 A
union all select '(B/3)+ C' formula, 10 B,5 C,0 A

begin --esse é o trecho que você precisa
    --declaro um cursor com os dados da tabela de amostra
    --aqui você irá substituir pelo seu select na sua tabela
    declare formulas cursor fast_forward for
    select formula
    from #formulas_table
    declare @formula varchar(100)

    --abre o cursor e atribiu o valor na variável @formula
    OPEN formulas  
    FETCH NEXT FROM formulas   
    INTO @formula

    --percorro o cursor até que não tenha mais dados
    while @@FETCH_STATUS = 0 
    BEGIN 
        --o exec pode ser usado para montar consultas concatenadas em uma string
        exec('select top 1 ' + @formula + ' from #formulas_table')

        --vai para o próximo item do cursor
        FETCH NEXT FROM formulas   
        INTO @formula
    END

    --fecha e libera o cursor de memória
    CLOSE formulas;  
    DEALLOCATE formulas; 
end --fim do trecho que você precisa

--dropa a tabela temporária, pois só precisei dela para montar o exemplo
drop table #formulas_table

  • Thanks for the script, but could you comment? because I didn’t quite understand this logic.

  • I added the comments to the script

  • I understood it now, thank you. But now with the concatenated values how could I execute the formula to get the result of it ? type (10/2) + 5 => Result: 10

  • but that’s what the exec('select top 1 ' + @formula + ' from #formulas_table') makes, it will concatenate the result of @formula and would look something like select top 1 (B/2)+ C from #formulas_table

  • @Apprentice Pablo’s suggestion is one of the solutions for calculating formulas. If you think it appropriate, you can store the result of each row in a temporary table and at the end list as a single dataset.

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.