[SOLVED] Recursive CTE can only work with temp table as initial SQL in Tableau

Recursive CTE can only work with temp table as initial SQL in Tableau

I'm trying to use recursive CTE in Tableau. I reazlized this doesn't work when running custom SQL, but I can create a temp table

CREATE TEMP TABLE temp_table as
WITH RECURSIVE missing_dates_cte (prop_code, missing_date, present_after) AS (
    SELECT prop_code, missing_date, present_after
    FROM missing_dates
    WHERE present_after IS NULL
    UNION ALL
    SELECT m.prop_code, m.missing_date, m.present_after
    FROM missing_dates m
    INNER JOIN missing_dates_cte cte ON m.prop_code = cte.prop_code
    WHERE m.missing_date > cte.missing_date
)
SELECT * FROM missing_dates_cte;

Then I go into "New Custom SQL" and do the following:

SELECT * FROM temp_table

No problems, so far...

What I want to know if this will work as intended each day? Does the initial SQL actually run whenever the data is queried or is this a one time "snapshot" when this connection is first created within Tableau desktop.

Currently the data is very small so I'm doing a live connection, but I'd also like to ensure this isn't an issue if I moved do an extract connection.

Solution

try creating a view just via SQL (not via Tableau) and then connect Tableau to that view - just like connecting to a table.

if the view is very slow for some reason, you could make it a materialized view and then schedule a regular job to refresh it. Only do that if you have a reason as the plain normal view is simpler to manage.

this way you don’t need initial sql or custom sql at all

— or you can make an extract and schedule periodic refreshes, which is similar to a materialized view, just stored in tableau instead of Postgres