search

linewalks / sql-formatter

A whitespace formatter for different query languages
https://zeroturnaround.github.io/sql-formatter/
MIT License
0 stars 0 forks source link

SQL Formatter 적용시 오류 발생하는 SQL #1

Open insutance opened 2 years ago

insutance commented 2 years ago

설명

SQLAlchemy를 통해 Incidence Rate 관련 쿼리를 추출했는데, 해당 쿼리를 Formatter 적용하려했지만 오류가 발생

WITH tar_start AS 
(SELECT cohort_id_407.index_date_all.person_id AS person_id, min(cohort_id_407.index_date_all.index_start_date) AS start_date 
FROM cohort_id_407.index_date_all GROUP BY cohort_id_407.index_date_all.person_id), 
tar_end AS 
(SELECT cohort_id_407.index_date_all.person_id AS person_id, max(cohort_id_407.index_date_all.index_start_date) AS start_date 
FROM cohort_id_407.index_date_all GROUP BY cohort_id_407.index_date_all.person_id), 
tar_period AS 
(SELECT tar_start.person_id AS person_id, tar_start.start_date + 0 AS tar_start_date, tar_end.start_date + 365 AS tar_end_date, CAST(False AS BOOLEAN) AS allow_prior_tar, CAST(True AS BOOLEAN) AS remove_prior_tar_all, coalesce(0, NULL) AS remove_prior_tar_days 
FROM tar_start JOIN tar_end ON tar_start.person_id = tar_end.person_id), 
agg_index AS 
(SELECT cohort_id_407.index_date_all.person_id AS person_id, min(cohort_id_407.index_date_all.index_start_date) AS index_date_earliest, max(cohort_id_407.index_date_all.index_start_date) AS index_date_latest 
FROM cohort_id_407.index_date_all GROUP BY cohort_id_407.index_date_all.person_id), 
tar_cohort_period AS 
(SELECT mdwalks.cohort.cohort_id AS cohort_id, mdwalks.cohort.person_id AS person_id, mdwalks.cohort.cohort_start_date AS cohort_start_date, mdwalks.cohort.cohort_end_date AS cohort_end_date 
FROM mdwalks.cohort 
WHERE mdwalks.cohort.cohort_id = 407), 
censor_date AS 
(SELECT tar_period.person_id AS person_id, synpuf5.death.death_date AS death_date, least(tar_period.tar_end_date, synpuf5.observation_period.observation_period_end_date, synpuf5.death.death_date, tar_cohort_period.cohort_end_date) AS censor_date 
FROM tar_period JOIN synpuf5.observation_period ON tar_period.person_id = synpuf5.observation_period.person_id JOIN tar_cohort_period ON tar_period.person_id = tar_cohort_period.person_id LEFT OUTER JOIN synpuf5.death ON tar_period.person_id = synpuf5.death.person_id), 
outcome AS 
(SELECT tar_period.person_id AS person_id, CASE WHEN (tar_period.allow_prior_tar IS false AND tar_period.remove_prior_tar_all IS true AND cohort_id_407.index_date_all.index_start_date <= tar_period.tar_start_date - '1 day, 0:00:00') THEN 1 WHEN (tar_period.allow_prior_tar IS false AND tar_period.remove_prior_tar_all IS false AND cohort_id_407.index_date_all.index_start_date BETWEEN tar_period.tar_start_date - tar_period.remove_prior_tar_days AND tar_period.tar_start_date - '1 day, 0:00:00') THEN 1 ELSE 0 END AS prior_outcome_yn, CASE WHEN (cohort_id_407.index_date_all.index_start_date BETWEEN tar_period.tar_start_date AND tar_period.tar_end_date) THEN cohort_id_407.index_date_all.index_start_date END AS o_start_date 
FROM tar_period JOIN cohort_id_407.index_date_all ON tar_period.person_id = cohort_id_407.index_date_all.person_id), 
agg_outcome AS 
(SELECT outcome.person_id AS person_id, max(outcome.prior_outcome_yn) AS prior_outcome_yn, min(outcome.o_start_date) AS o_min_date 
FROM outcome JOIN (SELECT outcome.person_id AS person_id, max(outcome.prior_outcome_yn) AS prior_outcome_yn 
FROM outcome GROUP BY outcome.person_id) AS anon_1 ON outcome.person_id = anon_1.person_id AND anon_1.prior_outcome_yn = 0 GROUP BY outcome.person_id), 
ir_raw_data AS 
(SELECT CAST(407 AS INTEGER) AS target_cohort_id, CAST(407 AS INTEGER) AS outcome_cohort_id, tar_period.person_id AS person_id, CASE WHEN (synpuf5.observation_period.observation_period_start_date > tar_period.tar_start_date) THEN 0 WHEN (synpuf5.observation_period.observation_period_end_date < tar_period.tar_start_date) THEN 0 WHEN (tar_period.tar_start_date > censor_date.censor_date) THEN 0 WHEN (tar_period.tar_start_date > tar_period.tar_end_date) THEN 0 WHEN (synpuf5.observation_period.observation_period_start_date > agg_index.index_date_earliest) THEN 0 WHEN (synpuf5.observation_period.observation_period_end_date < agg_index.index_date_earliest) THEN 0 WHEN (synpuf5.observation_period.observation_period_start_date > agg_index.index_date_latest) THEN 0 WHEN (synpuf5.observation_period.observation_period_end_date < agg_index.index_date_latest) THEN 0 WHEN (agg_outcome.person_id IS NULL) THEN 0 ELSE 1 END AS person_n, CASE WHEN (agg_outcome.prior_outcome_yn IS NULL) THEN 1 ELSE agg_outcome.prior_outcome_yn END AS prior_outcome_yn, agg_index.index_date_earliest AS index_date_earliest, agg_index.index_date_latest AS index_date_latest, tar_period.tar_start_date AS tar_start_date, tar_period.tar_end_date AS tar_end_date, tar_cohort_period.cohort_end_date AS cohort_end_date, synpuf5.observation_period.observation_period_end_date AS observation_period_end_date, censor_date.death_date AS death_date, censor_date.censor_date AS censor_date, agg_outcome.o_min_date AS o_min_date 
FROM tar_period JOIN agg_index ON tar_period.person_id = agg_index.person_id JOIN tar_cohort_period ON tar_period.person_id = tar_cohort_period.person_id JOIN censor_date ON tar_period.person_id = censor_date.person_id JOIN synpuf5.observation_period ON tar_period.person_id = synpuf5.observation_period.person_id LEFT OUTER JOIN agg_outcome ON tar_period.person_id = agg_outcome.person_id)
 SELECT ir_raw_data.target_cohort_id, ir_raw_data.outcome_cohort_id, ir_raw_data.person_id, ir_raw_data.person_n, ir_raw_data.prior_outcome_yn, ir_raw_data.index_date_earliest, ir_raw_data.index_date_latest, ir_raw_data.tar_start_date, ir_raw_data.tar_end_date, ir_raw_data.cohort_end_date, ir_raw_data.observation_period_end_date, ir_raw_data.death_date, ir_raw_data.censor_date, ir_raw_data.o_min_date, CASE WHEN (ir_raw_data.person_n = 1) THEN (least(ir_raw_data.o_min_date, ir_raw_data.censor_date) - ir_raw_data.tar_start_date) + 1 END AS o_time, CASE WHEN (ir_raw_data.person_n = 1 AND least(ir_raw_data.o_min_date, ir_raw_data.censor_date) = ir_raw_data.o_min_date) THEN 1 WHEN (ir_raw_data.person_n = 1 AND least(ir_raw_data.o_min_date, ir_raw_data.censor_date) = ir_raw_data.censor_date) THEN 0 END AS o_status 
FROM ir_raw_data

오류 발생

/snapshot/sql-formatter/lib/core/Tokenizer.js:193
          return v.slice(1);
                   ^

TypeError: Cannot read property 'slice' of undefined
    at parseKey (/snapshot/sql-formatter/lib/core/Tokenizer.js:193:20)
    at Tokenizer.getPlaceholderTokenWithKey (/snapshot/sql-formatter/lib/core/Tokenizer.js:237:21)
    at Tokenizer.getIdentNamedPlaceholderToken (/snapshot/sql-formatter/lib/core/Tokenizer.js:189:19)
    at Tokenizer.getPlaceholderToken (/snapshot/sql-formatter/lib/core/Tokenizer.js:184:19)
    at Tokenizer.getNextToken (/snapshot/sql-formatter/lib/core/Tokenizer.js:129:147)
    at Tokenizer.tokenize (/snapshot/sql-formatter/lib/core/Tokenizer.js:109:24)
    at StandardSqlFormatter.format (/snapshot/sql-formatter/lib/core/Formatter.js:89:38)
    at format (/snapshot/sql-formatter/lib/sqlFormatter.js:74:29)
    at Object.<anonymous> (/snapshot/sql-formatter/bin/sqlfmt.js:102:24)
    at Module._compile (pkg/prelude/bootstrap.js:1751:22)

image

westhyena commented 2 years ago

true, false가 소문자로 들어가 있을 때 발생합니다