Macro concept in SQL-CodePudding

Do any database engines have the concept of a C-like macro? Here would be an example where I'd just like to make it more readable:

SELECT
  SUM(Profit) AS Total,
  (SELECT AVG(Profit) FROM This 
     WHERE Category=This.Category AND Product=This.Product
     AND     PARSE_DATE('%M %d%, Y', CONCAT(This.Month ' 1, ' This.Year))
     BETWEEN PARSE_DATE('%M %d%, Y', CONCAT(This.Month ' 1, ' This.Year))
     AND PARSE_DATE('%M %d%, Y', CONCAT(This.Month ' 1, ' This.Year))-INTERVAL 3 MONTH
FROM Tbl

I would rather have something that looks like:

#define dt PARSE_DATE('%M %d%, Y', CONCAT(This.Month ' 1, ' This.Year))

SELECT
  SUM(Profit) AS Total,
  (SELECT AVG(Profit) FROM This
     WHERE Category=This.Category AND Product=This.Product
     AND dt BETWEEN dt AND (dt-INTERVAL 3 MONTH)
FROM Tbl

Does something like that exist or commonly-used in the major DBMSs?

CodePudding user response：

From Oracle 12, you can declare a function inside a sub-query factoring (WITH) clause:

WITH FUNCTION dt (month INT, year INT) RETURN DATE AS
BEGIN
  RETURN TO_DATE(year || '-' || month || '-01', 'YYYY-MM-DD');
END;
SELECT *
FROM   this
WHERE  dt(this.month, this.year)
         BETWEEN ADD_MONTHS(dt(this.month, this.year), -3)
         AND     dt(this.month, this.year);

db<>fiddle here

From Oracle 21, you can write SQL macros:

CREATE FUNCTION dt (month INT, year INT)
RETURN VARCHAR2 SQL_MACRO(SCALAR)
AS
BEGIN
  RETURN 'TO_DATE(year || ''-'' || month || ''-01'', ''YYYY-MM-DD'')';
END;
/

Then would use it as:

SELECT *
FROM   this
WHERE  dt(this.month, this.year)
         BETWEEN ADD_MONTHS(dt(this.month, this.year), -3)
         AND     dt(this.month, this.year);

And the query would get rewritten as:

SELECT *
FROM   this
WHERE  TO_DATE(this.year || '-' || this.month || '-01', 'YYYY-MM-DD')
         BETWEEN ADD_MONTHS(TO_DATE(this.year || '-' || this.month || '-01', 'YYYY-MM-DD'), -3)
         AND     TO_DATE(this.year || '-' || this.month || '-01', 'YYYY-MM-DD');