File name
Commit message
Commit date
File name
Commit message
Commit date
File name
Commit message
Commit date
File name
Commit message
Commit date
File name
Commit message
Commit date
File name
Commit message
Commit date
-- 운영 DB에 Phase 2(화면이 코드테이블만 보게 되는 단계)를 배포하기 전에 실행한다.
--
-- 결과가 한 줄이라도 나오면, 그 값을 active=false 코드로 code 테이블에 추가한 뒤
-- 배포해야 한다. 값을 지우거나 다른 값으로 바꾸지 않는 이유는 이미 그 값으로 저장된
-- 데이터가 있어서, 지우면 기존 화면에서 값이 조용히 사라지기 때문이다.
--
-- 실행 예:
-- psql -h 192.168.0.60 -U itnhub -d itnhub -f check-orphan-codes.sql
--
-- 고아값을 발견했을 때의 보존 예:
-- insert into code (group_id, code, label, sort_order, active)
-- values ('CONTACT_METHOD', '문자', '문자', 90, false);
select 'review_item.review_major' as source, review_major as value, count(*) as cnt
from review_item
where review_major is not null and review_major <> ''
and review_major not in (select code from code where group_id = 'REVIEW_MAJOR')
group by review_major
union all
select 'review_item.review_minor', review_minor, count(*)
from review_item
where review_minor is not null and review_minor <> ''
and review_minor not in (select code from code where group_id = 'REVIEW_MINOR')
group by review_minor
union all
select 'review_item.review_result', review_result, count(*)
from review_item
where review_result is not null and review_result <> ''
and review_result not in (select code from code where group_id = 'REVIEW_RESULT')
group by review_result
union all
select 'review_item.judged_kogl_type', judged_kogl_type, count(*)
from review_item
where judged_kogl_type is not null and judged_kogl_type <> ''
and judged_kogl_type not in (select code from code where group_id = 'KOGL_TYPE')
group by judged_kogl_type
union all
select 'review_item.kogl_type', kogl_type, count(*)
from review_item
where kogl_type is not null and kogl_type <> ''
and kogl_type not in (select code from code where group_id = 'KOGL_TYPE')
group by kogl_type
union all
select 'process_item.process_status', process_status, count(*)
from process_item
where process_status is not null and process_status <> ''
and process_status not in (select code from code where group_id = 'PROCESS_STATUS')
group by process_status
union all
select 'process_item.judged_kogl_type', judged_kogl_type, count(*)
from process_item
where judged_kogl_type is not null and judged_kogl_type <> ''
and judged_kogl_type not in (select code from code where group_id = 'KOGL_TYPE')
group by judged_kogl_type
union all
select 'process_item.prior_kogl_type', prior_kogl_type, count(*)
from process_item
where prior_kogl_type is not null and prior_kogl_type <> ''
and prior_kogl_type not in (select code from code where group_id = 'KOGL_TYPE')
group by prior_kogl_type
union all
select 'contact_log.method', method, count(*)
from contact_log
where method is not null and method <> ''
and method not in (select code from code where group_id = 'CONTACT_METHOD')
group by method
order by 1, 2;