CODEWITHSUNDEEP
sqlvariablesdb2
Totty.js
Totty.js

Reputation: 15841

How to use variables (for performance and maintenance) in inline SELECT SQL command in DB2?

I have a query like this:

SELECT a, very_costly_function(b), another_fn(very_costly_function(b)) from X

and you can notice that very_costly_function(b) is used twice, therefore if it could be set a variable like this would be nice:

SET FOR EACH ROW: Z = very_costly_function(b)
SELECT a, Z, another_fn(Z) from X

Upvotes: 0

Views: 299

Answers (2)

Rachcha
Rachcha

Reputation: 8816

SELECT a, fn_b, another_fn(fn_b) AS a_fn_b
  FROM (SELECT a, very_costly_function(b) AS fn_b
          FROM x)

This would save the overhead for you. Putting values into a variable and using it again can become a bit cumbersome for a compiler, you cannot, a lot of times, directly use such a query in your front-end or other higher-tier programs. If one single query does the job, use it!

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20804

Insert a, and very_costly_function(b) into a temp table. Then select a, the function result, and the other function (function result) from the temp table.

Upvotes: 1

Related Questions