reuse results from sql functions

Question

Take a look at the beginning of this query:

SELECT
   sum(decode(REGEXP_COUNT(tpdd.domain, 'thedomain.com'), 1, tpdd.size, 0, 0))  SizeClient,
   sum(decode(REGEXP_COUNT(tpdd.domain, 'thedomain.com'), 1, 0, 0, tpdd.size))  SizeThirdParty,
   ...

Is there a way to reuse the results of the "REGEXP_COUNT(tpdd.domain, 'thedomain.com')" function? I should hope that the oracle server is smart enough to do that but I can't guarantee it, and besides, the code would look better without the repeated code.

You could, presumably, move the calculation into the tpdd subquery (or transform the tpdd query into an inline view and add that). I wouldn't expect any vaguely recent version of Oracle to recompute the function call, however. — Justin Cave
– Justin Cave, Commented Dec 5, 2014 at 20:09

Hogan · Accepted Answer · 2014-12-05 20:40:07Z

4

You can use a CTE like this:

 WITH domain_regex as
 (
    SELECT tpdd.domain, REGEXP_COUNT(tpdd.domain,'thedomain.com') as regex_res
    FROM ...
 ) 
 SELECT
    sum(decode(d.regex_res, 1, tpdd.size, 0, 0))  SizeClient,
    sum(decode(d.regex_res, 1, 0, 0, tpdd.size))  SizeThirdParty,
    ...
 JOIN domain_regex d on tpdd.domain = d.domain

answered Dec 5, 2014 at 20:40

Hogan

70.7k10 gold badges83 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

reuse results from sql functions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related