안녕하세요.
다음 코딩이 오류는 안뜨고 데이터가 안뜨는 형태로 떠서 질문드립니다.
select
b.master_seq as visit_concept_id,
a.person_id as person_id,
9202 as visit_concept_id,
cast(to_char(a.hchk_year ||'0101', 23)as date) as visit_start_date,
null as visit_start_time,
cast(to_char(a.hchk_year ||'0101', 23)as date) as visit_end_date,
null as visit_end_time,
44818517 as visit_type_concept_id,
null as provider_id,
null as care_site_id,
b.master_seq as visit_source_value,
null as visit_source_concept_id
from cohort_cdm.NHID_GJ a JOIN cohort_cdm.seq_master b on a.person_id=b.person_id and a.hchk_year=b.hchk_year;
제생각에는
cast(to_char(a.hchk_year ||'0101', 23)as date) as visit_start_date,
null as visit_start_time,
cast(to_char(a.hchk_year ||'0101', 23)as date) as visit_end_date,
null as visit_end_time,
이부분을 고쳐야 될 것 같은데, 어떻게 고쳐야 할지 감이 안잡히네요.
코딩 고수분들 도움 요청드립니다.
/************************************** | |
--encoding : UTF-8 | |
--Author: 이성원 | |
--Date: 2017.01.26 | |
@NHISDatabaseSchema : DB containing NHIS National Sample cohort DB | |
@NHID_JK: JK table in NHIS NSC | |
@NHID_20T: 20 table in NHIS NSC | |
@NHID_30T: 30 table in NHIS NSC | |
@NHID_40T: 40 table in NHIS NSC | |
@NHID_60T: 60 table in NHIS NSC | |
@NHID_GJ: GJ table in NHIS NSC | |
--Description: Visit_occurrence 테이블 생성 | |
--Generating Table: VISIT_OCCURRENCE | |
***************************************/ | |
/************************************** | |
1. 테이블 생성 | |
***************************************/ | |
CREATE TABLE cohort_cdm.VISIT_OCCURRENCE ( --데이터 유형이 부적합 | |
visit_occurrence_id bigint primary key, | |
person_id integer not null, | |
visit_concept_id integer not null, | |
visit_start_date date not null, | |
visit_start_time time, | |
visit_end_date date not null, | |
visit_end_time time, | |
visit_type_concept_id integer not null, | |
provider_id integer, | |
care_site_id integer, | |
visit_source_value varchar(50), | |
visit_source_concept_id integer | |
); | |
/************************************** | |
2. 데이터 입력 | |
***************************************/ | |
insert into cohort_cdm.VISIT_OCCURRENCE ( | |
visit_occurrence_id, person_id, visit_concept_id, visit_start_date, visit_start_time, | |
visit_end_date, visit_end_time, visit_type_concept_id, provider_id, care_site_id, | |
visit_source_value, visit_source_concept_id | |
) | |
select | |
key_seq as visit_concept_id, | |
person_id as person_id, | |
case when form_cd in ('02', '04', '06', '07', '10', '12') and in_pat_cors_type in ('11', '21', '31') then 9203 --입원 + 응급 | |
when form_cd in ('02', '04', '06', '07', '10', '12') and in_pat_cors_type not in ('11', '21', '31') then 9201 --입원 + 입원 | |
when form_cd in ('03', '05', '08', '09', '11', '13', '20', '21', 'ZZ') and in_pat_cors_type in ('11', '21', '31') then 9203 --외래 + 응급 | |
when form_cd in ('03', '05', '08', '09', '11', '13', '20', '21', 'ZZ') and in_pat_cors_type not in ('11', '21', '31') then 9202 --외래 + 외래 | |
else 0 | |
end as visit_concept_id, | |
convert(date, recu_fr_dt, 112) as visit_start_date, | |
null as visit_start_time, | |
case when form_cd in ('02', '04', '06', '07', '10', '12') then DATEADD(DAY, vscn-1, convert(date, recu_fr_dt, 112)) | |
when form_cd in ('03', '05', '08', '09', '11', '13', '20', '21', 'ZZ') and in_pat_cors_type in ('11', '21', '31') then DATEADD(DAY, vscn-1, convert(date, recu_fr_dt, 112)) | |
else convert(date, recu_fr_dt, 112) | |
end as visit_end_date, | |
null as visit_end_time, | |
44818517 as visit_type_concept_id, | |
null as provider_id, | |
ykiho_id as care_site_id, | |
key_seq as visit_source_value, | |
null as visit_source_concept_id | |
from cohort_cdm.NHID_20T; | |
--건강검진 INSERT | |
insert into cohort_cdm.VISIT_OCCURRENCE ( | |
visit_occurrence_id, person_id, visit_concept_id, visit_start_date, visit_start_time, | |
visit_end_date, visit_end_time, visit_type_concept_id, provider_id, care_site_id, | |
visit_source_value, visit_source_concept_id | |
) | |
select | |
b.master_seq as visit_concept_id, | |
a.person_id as person_id, | |
9202 as visit_concept_id, | |
cast(CONVERT(VARCHAR, a.hchk_year+'0101', 23)as date) as visit_start_date, | |
null as visit_start_time, | |
cast(CONVERT(VARCHAR, a.hchk_year+'0101', 23)as date) as visit_end_date, | |
null as visit_end_time, | |
44818517 as visit_type_concept_id, | |
null as provider_id, | |
null as care_site_id, | |
b.master_seq as visit_source_value, | |
null as visit_source_concept_id | |
from cohort_cdm.NHID_GJ a JOIN cohort_cdm.seq_master b on a.person_id=b.person_id and a.hchk_year=b.hchk_year;
답변 감사합니다. 아주대학교 mssql 국민건강보험 서비스를 omop-cdm으로 전환하기 위한 oracle etl 코드입니다. 사실 목적도 알아야 되는데, 변환하느라 잊고 변환하는 과정만 해왔던것 같아요.. 말씀하신대로 to_char를 없애고 cast를 to date로 변환하였더니 from이 제위치에 없다고합니다.. 어떻게 하면좋을가요??
해당 구문의 목적은 제생각에는 환자의 방문한 날짜와 방문이 끝난날자를 불러오는 것 같습니다. |
감사합니다.!!
select
b.master_seq as visit_concept_id,
a.person_id as person_id,
9202 as visit_concept_id,
TO_DATE(a.hchk_year || '0101', 'yyyymmdd') AS visit_start_date,
null as visit_start_time,
TO_DATE(a.hchk_year || '0101', 'yyyymmdd') AS visit_end_date,
null as visit_end_time,
44818517 as visit_type_concept_id,
null as provider_id,
null as care_site_id,
b.master_seq as visit_source_value,
null as visit_source_concept_id
from cohort_cdm.NHID_GJ a JOIN cohort_cdm.SEQ_MASTER b on a.person_id=b.person_id and a.hchk_year=b.hchk_year;
이 구문에서 b.hchk_year; 이식별자가 부적합한 식별자라고 뜨는데
어떻게 변형하면 좋을가요?..