해당 death 테이블을 월의 말일로 / 날짜가 없는경우 해당년의 12월31일로 지정을 두 쿼리로 나누었습니다.
Commit을 할때 Insert into 부분이 중복되서 무결정 제약조건에 위배된다고 오류가 뜨는데용
이 부분에 있어서 어떻게 하면 좋을가요??
--날짜를 해당 월의 말일로 정의 | |
INSERT INTO cohort_cdm.DEATH (person_id, death_date, death_type_concept_id, cause_concept_id, | |
cause_source_value, cause_source_concept_id) | |
SELECT a.person_id AS PERSON_ID, | |
to_char(last_day(to_date(dth_ym||'01','yyyymmdd')),'yyyymmdd') as death_date, | |
38003618 as death_type_concept_id, | |
b.concept_id as cause_concept_id, | |
dth_code1 as cause_source_value, | |
NULL as cause_source_concept_id | |
from | |
(select STND_Y, PERSON_ID, SEX, AGE_GROUP, DTH_YM, rtrim(dth_code1) as DTH_CODE1, DTH_CODE2, SIDO, SGG, IPSN_TYPE_CD, CTRB_PT_TYPE_CD, DFAB_GRD_CD, DFAB_PTN_CD, DFAB_REG_YM from cohort_cdm.NHID_JK) a | |
left join death_mapping b | |
on a.dth_code1=b.kcdcode | |
WHERE a.dth_ym IS NOT NULL; | |
/*select * FROM | |
(select rtrim(dth_code1) as aa from cohort_cdm.NHID_JK where dth_code1 is not null and dth_code1 = 'J46') a | |
left join | |
(select kcdcode as bb from death_mapping where kcdcode = 'J46') b | |
on a.aa=b.bb; */ | |
--날짜 없는 경우 해당 년의 12월 31일로 death 정의 | |
INSERT INTO cohort_cdm.DEATH (person_id, death_date, death_type_concept_id, cause_concept_id, | |
cause_source_value, cause_source_concept_id) | |
SELECT a.person_id AS PERSON_ID, | |
STND_Y || '1231' AS death_date, | |
38003618 as death_type_concept_id, | |
b.concept_id as cause_concept_id, | |
dth_code1 as cause_source_value, | |
NULL as cause_source_concept_id | |
from | |
(select STND_Y, PERSON_ID, SEX, AGE_GROUP, DTH_YM, rtrim(dth_code1) as DTH_CODE1, DTH_CODE2, SIDO, SGG, IPSN_TYPE_CD, CTRB_PT_TYPE_CD, DFAB_GRD_CD, DFAB_PTN_CD, DFAB_REG_YM from cohort_cdm.NHID_JK) a | |
left join death_mapping b | |
on a.dth_code1=b.kcdcode | |
WHERE a.dth_ym IS NOT NULL; |