Question 89015 in PostgreSQL Question posted in
The official documentation can be found here.

Why do I keep getting a syntax error when I add another "with statement" to this query? – Postgresql

sorry if this is so obviously easy but I am new to this! I am trying to add another WITH statement to a query that works perfectly fine, but breaks when I edit it.

The query I am trying is:

--- 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%')

The query that follows after "final query" isn’t right I know – but I can’t even get past the problem of adding another "with" to get to the next part! I keep getting "syntax error at end of input" on the last line before — final query—. How do I fix this?

I have tried rearranging the order of the WITH statements, that didn’t work. I tried editing the final query code section but am getting nothing. So frustrated

Tags:

2

Answers


  1. 0

    Query seems ok. Please try after adding semicolon(;) at the end of query. If you are still getting this error then check the table names and column names.

    It would be great if you can provide any dbfiddle link.

    I have also made some changes to the query. Please try this one:

    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%');
    Login or Signup to reply.
  2. 0

    Here is some reverse engineered tables/columns based on your query: https://dbfiddle.uk/Df99nCG7 the query runs without error BUT I had to change the hardcoded date literal from DD-MM-YYYY into YYYY_MM_DD:

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

    If this does not resolve the issue check the DDL I have used, does it differ much from your real DDL? Perhaps replace my DDL with the original and run it again. If you still have error provide the URL to that fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search