Dremio Query Help! CTE/ Dynamic - Any alternative?

Dremio Cloud
1

Quite new to Dremio.
I have a few tables under Schema “Risk”. Some of those tables having column name either “END_DATE” or “DATE_LAST” (not both on same table). Now I would like to query all those tables for Max(END_DATE) or MAX(DATE_LAST) and make sure they all are same and its Current Date. If not then list that table name as well as the max date on that table.

I know in SQL Server, I’d have probably done that easily by using a combination of CTE and Dynamic Query as below… I am aware Dremio supports CTE but for dynamic query (or creating a temp table etc.) - I guess no??? Could someone please advise if this is feasible in Dremio at all?

DECLARE @currentDate DATE = GETDATE();

WITH MaxDateCTE AS (
    SELECT 
        TABLE_NAME,
        MAX(CASE WHEN COLUMN_NAME = 'END_DATE' THEN END_DATE ELSE NULL END) AS MaxEndDate,
        MAX(CASE WHEN COLUMN_NAME = 'DATE_LAST' THEN DATE_LAST ELSE NULL END) AS MaxDateLast
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = 'Risk'
    AND (COLUMN_NAME = 'END_DATE' OR COLUMN_NAME = 'DATE_LAST')
    GROUP BY TABLE_NAME
)
SELECT 
    TABLE_NAME,
    CASE 
        WHEN MaxEndDate IS NOT NULL AND MaxEndDate <> @currentDate THEN 'Max END_DATE: ' + CONVERT(VARCHAR, MaxEndDate)
        WHEN MaxDateLast IS NOT NULL AND MaxDateLast <> @currentDate THEN 'Max DATE_LAST: ' + CONVERT(VARCHAR, MaxDateLast)
        ELSE 'All dates are correct'
    END AS Result
FROM MaxDateCTE
WHERE MaxEndDate IS NOT NULL OR MaxDateLast IS NOT NULL;
2

@amukjee Dremio does not support parameterized SQL. The only place you are using currentDate is in the FILTER, you can directly use CURRENT_DATE() in a FILTER like the example below

SELECT * FROM "employees.parquet" WHERE hire_date >= '1991-01-01' and hire_date <= CURRENT_DATE()