# Dremio Query Help! CTE/ Dynamic - Any alternative?

**URL:** <https://community.dremio.com/t/dremio-query-help-cte-dynamic-any-alternative/11767>\
**Category:** Dremio Cloud\
**Created:** [April 29, 2024, 3:10pm UTC](https://community.dremio.com/t/dremio-query-help-cte-dynamic-any-alternative/11767 "2024-04-29T15:10:12Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![amukjee](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/amukjee/32/4965_2.png) [@amukjee](https://community.dremio.com/u/amukjee)\
**Post date:** [April 29, 2024, 3:10pm UTC](https://community.dremio.com/t/dremio-query-help-cte-dynamic-any-alternative/11767/1 "2024-04-29T15:10:12Z")

</div>

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?

```auto
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;

```

---

<div class="post-metadata">

**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\
**Post date:** [May 3, 2024, 8:30pm UTC](https://community.dremio.com/t/dremio-query-help-cte-dynamic-any-alternative/11767/2 "2024-05-03T20:30:08Z")

</div>

@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

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

```