96 lines
2.4 KiB
Markdown
96 lines
2.4 KiB
Markdown
|
```SQL
|
||
|
|
|
||
|
|
|
||
|
|
delete
|
||
|
|
from doc_dbreport_excel
|
||
|
|
where cold in ('테이블ID(물리명)', '데이터베이스명', '테이블 설명', 'No')
|
||
|
|
|
||
|
|
|
||
|
|
select row_number() over(order by cast(seq as integer)) as rownum, seq -- 시작순번
|
||
|
|
, nvl(LEAD(seq ) OVER(order by cast(seq as integer)), 469) -1 as next_seq -- 마지막순번
|
||
|
|
, cola, colb, colc
|
||
|
|
from doc_dbreport_excel ti
|
||
|
|
where cold = 1
|
||
|
|
order by cast(seq as integer)
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
update doc_dbreport_excel a,
|
||
|
|
(
|
||
|
|
select row_number() over(order by cast(seq as integer)) as rownum, seq -- 시작순번
|
||
|
|
, nvl(LEAD(seq ) OVER(order by cast(seq as integer)), 469) -1 as next_seq -- 마지막순번
|
||
|
|
, cola, colb, colc
|
||
|
|
from doc_dbreport_excel ti
|
||
|
|
where cold = 1
|
||
|
|
order by cast(seq as integer) ) b
|
||
|
|
set a.cola = b.cola,
|
||
|
|
a.colb = b.colb,
|
||
|
|
a.colc = b.colc
|
||
|
|
where cast(a.seq as integer) >= b.seq
|
||
|
|
and cast(a.seq as integer) <= b.next_seq
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
---------------------------------------------------------------------------
|
||
|
|
|
||
|
|
|
||
|
|
drop table tmp_doc_tables
|
||
|
|
|
||
|
|
|
||
|
|
create table tmp_doc_tables as
|
||
|
|
select 99 as seq ,c.*
|
||
|
|
from (
|
||
|
|
select distinct
|
||
|
|
colq
|
||
|
|
, '30G'
|
||
|
|
, cola, colb
|
||
|
|
, colc
|
||
|
|
, case when colq = 'TS_EAI' then 'LK(EAI 연계정보)'
|
||
|
|
when colq = 'TS_TRACK' then 'IC(항적정보)'
|
||
|
|
when colq = 'TS_CM' then 'CM'
|
||
|
|
when colq = 'TS_IC' then 'IC'
|
||
|
|
when colq = 'TS_DT' then 'DT' end as gubun
|
||
|
|
, yebi1
|
||
|
|
from doc_dbreport_excel
|
||
|
|
order by cast(seq as integer), colq ) c
|
||
|
|
|
||
|
|
|
||
|
|
--------------------------------------------------------------------------
|
||
|
|
|
||
|
|
|
||
|
|
select *
|
||
|
|
from tmp_doc_tables
|
||
|
|
|
||
|
|
select seq, gubun, yebi1, colb, colc, '' as super, '' as cid, cola as table_id, '' as bigo
|
||
|
|
from tmp_doc_tables
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
select b.gubun -- 영역
|
||
|
|
, b.yebi1 -- ett id
|
||
|
|
, a.colB -- ett 명
|
||
|
|
, a.colc -- ett설명
|
||
|
|
, '' as class_id
|
||
|
|
, '' as colass_nm
|
||
|
|
, a.colA -- 테이블 id
|
||
|
|
, a.colB -- 테이블 명
|
||
|
|
-- , a.cold -- no
|
||
|
|
, a.colg -- 속성명
|
||
|
|
, '' as dong
|
||
|
|
, a.colH -- 타입
|
||
|
|
, a.colI -- 길이
|
||
|
|
, case when a.colj = 'O' then 'NOT NULL' else 'NULL' end as nullyn -- null
|
||
|
|
, case when a.colk = 'O' then 'PK' else '' end as pkyn -- pk
|
||
|
|
, case when a.colL = 'O' then 'FK' else '' end as fkyn -- fk
|
||
|
|
, case when a.colM = 'O' then 'Y' else '' end as idx
|
||
|
|
, a.coln as def
|
||
|
|
, a.colo as jy
|
||
|
|
, a.colq
|
||
|
|
, a.colR
|
||
|
|
from doc_dbreport_excel a, tmp_doc_tables b
|
||
|
|
where a.cola = b.cola
|
||
|
|
order by cast(a.seq as integer), cast(a.colD as integer)
|
||
|
|
|
||
|
|
|
||
|
|
```
|