I have a query i've written on snowflake that is running succesfully, the query utilizes multiple temp tables to derive the main final table (each temp table joins the previous temp table)
When i bring the query into PowerBi, i'm getting a syntax error that seems to begin wheneve a iremove semicolon 'syntax error line 104 at position 0 unexpected 'CREATE'.'
Below is an example fo the multi Temp Table,
**most of the naming convention and table name have been modified for confidintiality so the join may look wrong, on snowflake everything is working correctly and i'm able to retrieve the FINAL TABLE**
The temp tables needed for joins
1. task_history
2. temp_tasks
3. Select_Marketing_Task
Final temp Table
1. Client_temp
The Query begins
---------------------------------------------------------------------------------------------------------------------------
-- We need a table with a single task historyid to facilitate joins
---------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE TEMPORARY TABLE task_history AS ------ we may not need this table
SELECT -----
th_tsk.TASK_ID as task_id
,th_tsk.HISTORY_TIME_ID as history_id
,tm_tsk.TASK_INITIATED_DATETIME as date_task_start
FROM
EDW_PROD.RETAIL_ENH.ENH_DIM_TASK_HISTORY th_tsk
INNER JOIN
EDW_PROD.RETAIL_ENH.ENH_FACT_TASK_HISTORY tht_tsk
ON th_tsk.TASK_HISTORY_SK = tht_tsk.TASK_HISTORY_SK
---------------------------------------------------------------------------------------------------------------------------
--- Temp table for latest current step for tasks
----------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE TEMPORARY TABLE temp_tasks AS
WITH RankedTasks AS (
SELECT
dm_tsk.task_id
,tm_tsk.TASK_INITIATED_DATETIME
,dm_tsk.file_id
,ROW_NUMBER() OVER (PARTITION BY dm_tsk.task_id ORDER BY th_tsk.HISTORY_TIME_ID DESC) AS row_num
FROM
EDW_PROD.RETAIL_ENH.ENH_FACT_TASK tm_tsk
INNER JOIN
EDW_PROD.RETAIL_ENH.ENH_DIM_TASK dm_tsk
ON dm_tsk.task_sk = tm_tsk.task_sk
INNER JOIN
EDW_PROD.RETAIL_ENH.ENH_FACT_TASK_HISTORY tht_tsk
ON tm_tsk.TASK_SK = tht_tsk.TASK_SK
)
SELECT
task_id
,TASK_INITIATED_DATETIME
,file_id
FROM
RankedTasks
WHERE
row_num = 1;
---------------------------------------------------------------------------------------------------------------------------
--- Temp table for marketing specialist
----------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE TEMPORARY TABLE Select_Marketing_Task AS
SELECT
distinct
tm_tsk.TASK_ID as task_id
,tm_tsk.TASK_INITIATED_DATETIME as date_task_start
,tm_tsk.TASK_COMPLETED_DATETIME as date_task_end
,tm_tsk.TASK_SUBMISSION_DATETIME as date_submission
FROM
temp_tasks tm_tsk
INNER JOIN
EDW_PROD.RETAIL_ENH.ENH_DIM_TASK dim_tsk
ON tm_tsk.latest_task_history_id = dim_tsk.latest_task_history_id
LEFT JOIN
EDW_PROD.ENTERPRISE_ENH.ENH_DIM_ORGANIZATION dim_org
ON fct_cli.ACTIVE_ORGANIZATION_SK = dim_org.ORGANIZATION_SK
order by task_id desc
---------------------------------------------------------------------------------------------------------------------------
--- Temp table for Client, will currently use the "Select_Marketing_Task" temp table, and once its added as a ERP table will use that
----------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE TEMPORARY TABLE Client_temp AS
SELECT
distinct
CLIENT_SAGITTA_NUMBER as client_sagitta_number
,CLIENT_CODE as client_code
,STATUS_CODE1
,SIC_CODE
FROM
Select_Marketing_Task tmp_tsk
INNER JOIN
EDW_PROD.RETAIL_ENH.ENH_DIM_CLIENT dim_cli
ON tmp_tsk.CLIENT_SAGITTA_NUMBER = dim_cli.CLIENT_CODE
---------------------------------------------------------------------------------------------------------------------------
--- Final table
----------------------------------------------------------------------------------------------------------------------------
select *
from Client_temp
