更新时间：2024-04-18 GMT+08:00
系统视图
dbc.columnsV和dbc.IndicesV
输入 |
输出 |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
SELECT A.ColumnName AS V_COLS
,A.columnname || ' ' ||CASE WHEN columnType in ('CF','CV')
THEN CASE WHEN columnType='CV' THEN 'VAR' ELSE ''
END||'CHAR('||TRIM(columnlength (INT))||
') CHARACTER SET LATIN'||
CASE WHEN UpperCaseFlag='N'
THEN ' NOT' ELSE ''
END || ' CASESPECIFIC'
WHEN columnType='DA' THEN 'DATE'
WHEN columnType='TS' THEN 'TIMESTAMP(' || TRIM(DecimalFractionalDigits)||')'
WHEN columnType='AT' THEN 'TIME('|| TRIM(DecimalFractionalDigits)||')'
WHEN columnType='I' THEN 'INTEGER'
WHEN columnType='I1' THEN 'BYTEINT'
WHEN columnType='I2' THEN 'SMALLINT'
WHEN columnType='I8' THEN 'BIGINT'
WHEN columnType='D' THEN 'DECIMAL('||TRIM(DecimalTotalDigits)||','||TRIM(DecimalFractionalDigits)||')'
ELSE 'Unknown'
END||CASE WHEN Nullable='Y'
THEN '' ELSE ' NOT NULL' END||'0A'XC AS V_ColT - ,B.ColumnName AS V_PICol --获得目标表主索引
FROM dbc.columnsV A LEFT JOIN dbc.IndicesV B
ON A.columnName = B.columnName AND B.IndexType IN ('Q','P')
AND B.DatabaseName = '${V_TDDLDB}' AND B.tablename='${TARGET_TABLE}'
WHERE A.databasename='${V_TDDLDB}' AND A.tablename = '${TARGET_TABLE}'
AND A.columnname NOT IN ( 'ETL_JOB_NAME' ,'ETL_TX_DATE'
,'ETL_PROC_DATE'
)
ORDER BY A.columnid;
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
DECLARE lv_mig_V_COLS TEXT;
lv_mig_V_ColT TEXT;
lv_mig_V_PICol TEXT;
BEGIN
SELECT STRING_AGG(A.ColumnName, ',')
, STRING_AGG(A.columnname || ' ' ||CASE WHEN columnType in ('CF','CV')
THEN CASE WHEN columnType='CV' THEN 'VAR' ELSE ''
END||'CHAR('||TRIM(mig_td_ext.mig_fn_castasint(columnlength))||
') /*CHARACTER SET LATIN*/'||
CASE WHEN UpperCaseFlag='N'
THEN ' NOT' ELSE ''
END || ' /*CASESPECIFIC*/'
WHEN columnType='DA' THEN 'DATE'
WHEN columnType='TS' THEN 'TIMESTAMP(' || TRIM(DecimalFractionalDigits)||')'
WHEN columnType='AT' THEN 'TIME('|| TRIM(DecimalFractionalDigits)||')'
WHEN columnType='I' THEN 'INTEGER'
WHEN columnType='I1' THEN 'BYTEINT'
WHEN columnType='I2' THEN 'SMALLINT'
WHEN columnType='I8' THEN 'BIGINT'
WHEN columnType='D' THEN 'DECIMAL('||TRIM(DecimalTotalDigits)||','||TRIM(DecimalFractionalDigits)||')'
ELSE 'Unknown'
END||CASE WHEN Nullable='Y'
THEN '' ELSE ' NOT NULL' END||E'\x0A', ',') , STRING_AGG(B.ColumnName, ',')
INTO lv_mig_V_COLS, lv_mig_V_ColT, lv_mig_V_PICol
FROM mig_td_ext.vw_td_dbc_columnsV A LEFT JOIN mig_td_ext.vw_td_dbc_IndicesV B
ON A.columnName = B.columnName AND B.IndexType IN ('Q','P')
AND B.DatabaseName = 'public' AND B.tablename='emp2'
WHERE A.databasename='public' AND A.tablename = 'emp2';
-- ORDER BY A.columnid;
END;
/
|
|
