1

I need to use the computed values (after 'as' keyword) of two functions in sql in the next statement. I want to use it because the time of computations decreases x2 in this case.

I have the following statement: 

SELECT f1() as f_1, f2() as f_2, f_1 - f_2 as f1_minus_f2 FROM mytable

where f1(), f2() - some functions

user2783834
  • 17
  • 4

1 Answers1

2

1) You can use expression itself (if functions are deterministic):

SELECT f1() as f_1, f2() as f_2, f1() - f2() as f1_minus_f2 
FROM mytable

2) Use subquery:

SELECT sub.f_1, sub.f_2, sub.f_1 - sub.f_2 as f1_minus_f2
FROM (
   SELECT f1() as f_1, f2() as f_2
   FROM mytable
) sub

The reason you cannot use it as you want is all-at-once rule:

"All-at-Once Operations" means that all expressions in the same logical query process phase are evaluated logically at the same time.

Lukasz Szozda
  • 139,860
  • 19
  • 198
  • 228
  • 1
    The derived table alternative seems to be safer. Who knows how OP's dbms optimize things (And are the functions deterministic or not?) – jarlh Nov 20 '15 at 14:32
  • 1
    Thank you! The speed of computations really increased x2. – user2783834 Nov 20 '15 at 14:54
  • @user2783834 You can [accept my answer](http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work/5235#5235) if your question has been solved :) – Lukasz Szozda Nov 20 '15 at 15:10