添加WITH子句至SQL查询时持续出现语法错误，如何解决？

我是SQL新手，原本正常运行的查询在添加另一个WITH子句后报错，错误信息为syntax error at end of input，错误位置在--- final query---之前的最后一行。调整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%')

问题原因及修复方法 #

问题出在WITH子句块和最终SELECT语句之间的注释位置：在大多数SQL方言中，WITH子句定义完成后必须直接衔接主查询（SELECT/INSERT/UPDATE等），不能在中间插入注释或空行导致语法逻辑断开。

修复步骤 #

• 将--- final query ----注释移动到主SELECT语句的上方，或者直接删除该注释，确保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%')

额外提示 #

你定义的fixed_districtCTE在主查询中未被使用，若不需要可以直接删除以减少冗余；如果后续需要使用，只需在主查询中通过JOIN关联该CTE即可。

内容的提问来源于stack exchange，提问作者gisthisgisthat