1

With CTE it is possible to define 'on he fly' custom tables that exists only during the execution of the current query: is it possible to do the same for functions?

Example use case

I have a query with complex where conditions that i have to replicate in the group by section of the query so it would be great to encapsulate this grouping logic in its own portion of code and be reused anywhere is needed, exactly as with CTE.

1 Answer 1

4

No it's not possible. Also it's generally better to avoid function use, especially in predicates, in a relational language like SQL, mostly from a performance perspective.

Instead what you can do is use a CTE (as you're familiar with) to define the complex logic once as columns, then subsequently reference those columns in your WHERE and GROUP BY clauses, without having to repeat logic.

For example:

WITH CTE AS
(
    SELECT
        Column1,
        Column2,
        Column1 + Column2 AS NewColumn,
        CASE 
            WHEN Column3 > Column4 THEN 1
            ELSE 0
        END AS IsColumn3Greater
    FROM YourTable
)

SELECT
    Column1,
    NewColumn
FROM CTE
WHERE IsColumn3Greater = 1
GROUP BY Column1, NewColumn
    

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.