All geek questions in one place

LocalVariable inside data-retrieve SELECT-SQL

I am making a stored procedure that has a SELECT clause by selecting multiple columns from some related tables. For one of these quantities, the value depends on the condition with CASE.

SELECT DISTINCT table1.col1 as var1, table2.col2 as var2, CASE WHEN (FLOOR(table3.col3/0.2)*0.2) > 20 THEN 20 WHEN (FLOOR(table3.col3/0.2)*0.2) <= 20 THEN FLOOR(table3.col3/0.2)*0.2 ELSE table3.col4 -- selecting another value is the -- value in table3.col3 is null END as var3 FROM ... WHERE ...

As you can see in the selection, I do three times the calculation of FLOOR (table3.col3 / 0.2) * 0.2. Is it possible to store this calculated value in the WITHIN THE SELECT variable, and then use this variable in conditions?

Thanks in advance,

Jeroen

+4
source share
3 answers

No, you can reduce it to 2 calls, as shown below. But no matter what you do, it will be more expensive than it really is, since you do it very quickly. You can think of something to improve when you really have heavy operations. It is just nothing. You can also make the get_min function, but again I don’t think it is worth it.

With two calls:

 case when table3.col3 then table3.col4 when (FLOOR(table3.col3/0.2)*0.2) > 20 THEN 20 else FLOOR(table3.col3/0.2)*0.2 end

With min function:

 case when table3.col3 then table3.col4 else dbo.get_min(FLOOR(table3.col3/0.2)*0.2,20) end

As a side note, the min function should be built, which I mean (user-defined function).

+1
source

There is a way to do this by selecting it in the table links from table3 , instead of ... FROM table3 , you can do this:

 SELECT ... CASE WHEN (t3.flooredCol3) > 20 THEN 20 WHEN (t3.flooredCol3) <= 20 THEN t3.flooredCol3 .... FROM ( SELECT col1, ..., FLOOR(table3.col3 / 0.2) * 0.2 AS flooredCol3 FROM table3 ) t3 -- the rest of the table references
0
source

You can make a subquery

  SELECT *, FloorCalc FROM ( select *, (FLOOR(table3.col3/0.2)*0.2)) as FloorCalc from table3 ...) v

However, it is possible that the query optimizer will automatically cache the calculation results.

0
source

Source: https://habr.com/ru/post/1435009/

More articles:


All Articles