CODEWITHSUNDEEP
sqlnaming-conventionsquery-performance
morepenguins
morepenguins

Reputation: 1277

How to Define Variable for an Equation in SQL Query

I am a SQL newbie and I'm trying to query an online database for a subset. I know that the data I am interested in is bounded by an equation using two of the columns, and I want to query on the equation.

If I repeat the calculation each time I want to bound it, the query works, but I want to save time on the execution by saving the equation as a variable.

Here is the code that works by repeating the equation:

SELECT  *

FROM    online_database

WHERE   rnn < 8
AND     mol > 12
AND     1 + 0.5*rnn + 0.1*mol < 6
AND     1 + 0.5*rnn + 0.1*mol > 0.2

Here is an approximation of what I'm trying to do (this doesn't work):

SELECT  *

FROM    online_database

WHERE   rnn < 8
AND     mol > 12
AND     (1 + 0.5*rnn + 0.1*mol) as eqt < 6
AND     eqt > 0.2

Any advice is greatly appreciated!

Upvotes: 0

Views: 1454

Answers (1)

Frederic
Frederic

Reputation: 1028

You can create a computed column,

or should create a column and set insert/update triggers to calculate the value (in this way you can create an index in the result if needed)

or create a view with a column with the formula value,

or create a common table expression,

with cte as (select *, 1 + 0.5*rnn + 0.1*mol as computed
from (values (0.01, -10), (0.5, 0.2), (10, 20) ) as V (mol, rnn))

select * from cte
where computed < 6
and computed > 0.2

or in a not so pretty way, you can use outer apply

select *
from (values (0.01, -10), (0.5, 0.2), (10, 20) ) as V (mol, rnn)
outer apply (select (1 + 0.5*rnn + 0.1*mol) as computed_value) as calc
where calc.computed_value < 6
and calc.computed_value > 0.2

see working fiddle with outer apply and cte sample

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=bf8f93681ca90fad4c585e23bcf3b463

Upvotes: 1

Related Questions