46 lines
992 B
Markdown
46 lines
992 B
Markdown
|
```SQL
|
||
|
|
|
||
|
|
-- 도메인 추출
|
||
|
|
-- create table domain_items as
|
||
|
|
select distinct CollumnNm, CollumnID, replace(concat(`Type` , '(', nvl(len,''), ')'), '()', '') as col_typ
|
||
|
|
from table_items
|
||
|
|
where ETT_GBN <> 'EAI_MDD'
|
||
|
|
order by CollumnNm
|
||
|
|
|
||
|
|
-- 값의범위 업데이트
|
||
|
|
update domain_items a,
|
||
|
|
(select distinct CollumnID , CollumnNm , bigo
|
||
|
|
from table_items
|
||
|
|
where ETT_GBN <> 'EAI_MDD'
|
||
|
|
and (bigo like '%[공통코드]%'
|
||
|
|
or bigo like '%:%' ) ) b
|
||
|
|
set item_code_info = b.bigo
|
||
|
|
where a.CollumnID = b.collumnID
|
||
|
|
|
||
|
|
|
||
|
|
-- 도메인정의서 엑셀 변환
|
||
|
|
|
||
|
|
select upper(replace(CollumnNm , ' ' , '')) as CollumnNm , CollumnID , col_typ , item_code_info
|
||
|
|
from domain_items
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
select colf, max(colg), max(colh), max(coli), max(coln), max(colp)
|
||
|
|
from doc_dbreport_excel
|
||
|
|
where colq <> 'TS_EAI'
|
||
|
|
group by colf
|
||
|
|
order by colf
|
||
|
|
|
||
|
|
|
||
|
|
-------------------------
|
||
|
|
|
||
|
|
|
||
|
|
select CollumnID, max(CollumnNm), max(`Type`), max(len), max(Def), max(bigo)
|
||
|
|
from table_items
|
||
|
|
where ETT_GBN <> 'EAI_MDD'
|
||
|
|
group by CollumnID
|
||
|
|
order by CollumnID
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
```
|