为什么在向此查询添加另一个"with statement"时不断收到语法错误？

Question

抱歉，如果这是如此明显容易，但我是新的!我正试图将另一个WITH语句添加到一个查询中，该查询工作得很好，但当我编辑它时却中断了。

我正在尝试的查询是:

--- select the term name ---
with  term_name as (select dfs.k_form_submission,
dfs.k_form,
dfs.k_district as "district_id",
dff.form_name,
ffr.question_response as "term",
dfs.submitted_at
from 
prod_wh_pls.dim_pls_fa_forms dff
inner join 
prod_wh_pls.dim_pls_fa_submissions dfs
on 
dfs.k_form = dff.k_form
inner join 
prod_wh_pls.fct_pls_fa_responses ffr
on 
dfs.k_form_submission = ffr.k_form_submission
where 
(dff.k_form = 423614 or dff.k_form = 423615 or dff.k_form = 423608 or dff.k_form = 423613 or dff.k_form = 423670 or dff.k_form = 423706)
and 
(ffr.question_name = 'term' or ffr.question_name = 'session_name')
and 
dfs.k_form_submission != 457891),
fixed_district as (
select case when ffr.question_response = '2103480' then '16f7e40689605582e8fcfe7bc52481c4' else dfs.k_district end as "k_district", ffr.question_response as "nces_id", ffr.k_form_submission
from 
prod_wh_pls.fct_pls_fa_responses ffr
inner join 
prod_wh_pls.dim_pls_fa_submissions dfs
on 
ffr.k_form_submission = dfs.k_form_submission
where 
(ffr.question_name) = 'nces_id' and (ffr.k_form = 423706))
--- final query ----
select 
dfs.k_form_submission, dfs.k_form,
dfs.k_district as "district_id",
dff.form_name,
ffr.question_name,
ffr.question_response,
dfs.submitted_at,
(case when dfs.submitted_at < date '07-15-2022' then '2021-2022'
else tna.term end) as "term",
dfs.is_latest_submission,
dfs.completion_time
from 
prod_wh_pls.dim_pls_fa_forms dff
inner join 
prod_wh_pls.dim_pls_fa_submissions dfs
on 
dfs.k_form = dff.k_form
inner join 
prod_wh_pls.fct_pls_fa_responses ffr
on 
dfs.k_form_submission = ffr.k_form_submission
left join 
term_name tna
on 
tna.k_form_submission = dfs.k_form_submission
where 
(dff.k_form = 423614 or dff.k_form = 423615 or dff.k_form = 423608 or dff.k_form = 423613 or dff.k_form = 423670 or dff.k_form = 423706)
and 
dfs.k_form_submission != 457891
and 
dfs.k_district is not null
and 
(dfs.completion_time not ilike '%day%' and dfs.completion_time not ilike '%hr%')

"final query"之后的查询是不对的，我知道——但我甚至无法克服在";"后面再加一个";"这个问题。才能进入下一部分!我总是在输入结束时出现"语法错误"。在最后一行——最后一个查询——之前。我该如何解决这个问题?

我已经尝试重新安排WITH语句的顺序，这不起作用。我试着编辑最后的查询代码部分，但我什么也没得到。因此沮丧

Answer 1

查询正常。请在查询末尾添加分号(;)后重试。如果您仍然收到此错误，请检查表名和列名。

如果你能提供dbfiddle的链接就太好了。

我也对查询做了一些更改。请试试这个:

WITH 
term_name AS (
SELECT 
dfs.k_form_submission,
dfs.k_form,
dfs.k_district AS district_id,
dff.form_name,
ffr.question_response AS term,
dfs.submitted_at
FROM 
prod_wh_pls.dim_pls_fa_forms dff
INNER JOIN prod_wh_pls.dim_pls_fa_submissions dfs ON dfs.k_form = dff.k_form
INNER JOIN prod_wh_pls.fct_pls_fa_responses ffr ON dfs.k_form_submission = ffr.k_form_submission
WHERE 
dff.k_form IN (423614, 423615, 423608, 423613, 423670, 423706)
AND ffr.question_name IN ('term', 'session_name')
AND dfs.k_form_submission != 457891
),
fixed_district AS (
SELECT 
CASE 
WHEN ffr.question_response = '2103480' THEN '16f7e40689605582e8fcfe7bc52481c4' 
ELSE dfs.k_district 
END AS k_district, 
ffr.question_response AS nces_id, 
ffr.k_form_submission
FROM 
prod_wh_pls.fct_pls_fa_responses ffr
INNER JOIN prod_wh_pls.dim_pls_fa_submissions dfs ON ffr.k_form_submission = dfs.k_form_submission
WHERE 
ffr.question_name = 'nces_id' 
AND ffr.k_form = 423706
)
SELECT 
dfs.k_form_submission, 
dfs.k_form,
dfs.k_district AS district_id,
dff.form_name,
ffr.question_name,
ffr.question_response,
dfs.submitted_at,
(CASE 
WHEN dfs.submitted_at < date '07-15-2022' THEN '2021-2022'
ELSE tna.term 
END) AS term,
dfs.is_latest_submission,
dfs.completion_time
FROM 
prod_wh_pls.dim_pls_fa_forms dff
INNER JOIN prod_wh_pls.dim_pls_fa_submissions dfs ON dfs.k_form = dff.k_form
INNER JOIN prod_wh_pls.fct_pls_fa_responses ffr ON dfs.k_form_submission = ffr.k_form_submission
LEFT JOIN term_name tna ON tna.k_form_submission = dfs.k_form_submission
WHERE 
dff.k_form IN (423614, 423615, 423608, 423613, 423670, 423706)
AND dfs.k_form_submission != 457891
AND dfs.k_district IS NOT NULL
AND (dfs.completion_time NOT ILIKE '%day%' AND dfs.completion_time NOT ILIKE '%hr%');

Answer 2

以下是一些基于您的查询的反向工程表/列:https://dbfiddle.uk/Df99nCG7查询运行没有错误，但我不得不将硬编码日期文字从DD-MM-YYYY更改为YYYY_MM_DD:

, (CASE WHEN dfs.submitted_at < DATE '2022-07-15' THEN '2021-2022' ELSE tna.term END) AS "term"

如果这不能解决问题，请检查我使用的DDL，它与您实际的DDL有很大差异吗?也许用原来的DDL替换我的DDL，然后再运行一次。如果您仍然有错误，请提供URL给该提琴。