using an alias of a function in a sql condition-CodePudding

I have something like this:

SELECT 
    cansa1.NAME,
    mod(cansa1.PRODUCT_ID, 1000000) prodIdHash
                    
FROM CANSA_TABLE cansa1

INNER JOIN CUSER_TABLE cuser1 ON cansa1.PRODUCT_ID = cuser1.PRODUCT_ID
AND mod(cansa1.PRODUCT_ID, 1000000) = cuser1.PRODUCT_HASH

This query is working, but I want replace the second occurrence (in the inner join) of the mod() function, to avoid execute it two times. I tried replace it by the alias in the select clause but not works. Any idea of that I can use to make this query don't repeat the mod() function?

Sorry by my english

CodePudding user response：

Don't worry about executing it twice, the SQL engine will optimize the query and will decide whether the function value is cached or it executes twice and can end up re-writing the query so that what is executed has a different structure than the written query because it has determined that it would be more efficient.

If you really want to try to rewrite it then:

SELECT c.NAME,
       c.prodIdHash
FROM   (
         SELECT name,
                mod(PRODUCT_ID, 1000000) As prodIdHash
         FROM   CANSA_TABLE
       ) c
       INNER JOIN CUSER_TABLE u
       ON (   c.PRODUCT_ID = u.PRODUCT_ID
          AND c.prodIdHash = u.PRODUCT_HASH )

However, the SQL engine may rewrite the query and push the function to the outer scope so you may need a seemingly irrelevant filter condition to materialize the inner query and force the calculation not to be rewritten:

SELECT c.NAME,
       c.prodIdHash
FROM   (
         SELECT name,
                mod(PRODUCT_ID, 1000000) As prodIdHash
         FROM   CANSA_TABLE
         WHERE  ROWNUM > 0
       ) c
       INNER JOIN CUSER_TABLE u
       ON (   c.PRODUCT_ID = u.PRODUCT_ID
          AND c.prodIdHash = u.PRODUCT_HASH )

However, this really seems like a case of premature optimisation. You should check if there is actually a problem first before you try and apply an optimisation that probably is not needed.

CodePudding user response：

You can use a derived table (i.e. a subquery in the FROM clause):

SELECT dt.NAME, dt.prodIdHash
FROM
  (SELECT 
       cansa1.NAME,
       mod(cansa1.PRODUCT_ID, 1000000) prodIdHash
   FROM CANSA_TABLE cansa1) dt
INNER JOIN CUSER_TABLE cuser1 ON dt.PRODUCT_ID = cuser1.PRODUCT_ID
  AND dt.prodIdHash = cuser1.PRODUCT_HASH