Code:
- with selected (system_table_name, system_table_schema)
- as (values ('MYTABLE','MYLIB') )
- , tbl as (
- select
- case
- when data_type = 'DECIMAL' then 'packed'
- when data_type = 'NUMERIC' then 'zoned'
- when data_type = 'TIMESTMP' then 'timestamp'
- when data_type = 'INTEGER' then 'int'
- else lower(data_type)
- end
- concat case
- when data_type = 'INTEGER' and length >= 8 then '(20'
- when data_type = 'INTEGER' and length >= 4 then '(10'
- when data_type = 'INTEGER' and length >= 2 then '(5'
- when data_type in ('TIMESTMP','DATE','TIME') then ''
- else '(' concat length
- end
- concat case
- when numeric_scale is null then ''
- when data_type = 'INTEGER' then ''
- else ':' concat numeric_scale
- end
- concat case
- when data_type in ('TIMESTMP','DATE','TIME') then ';'
- else ');'
- end
- as rpg_type
- ,system_column_name, length, numeric_scale,
- column_text, column_name, ordinal_position
- from qsys2.syscolumns syscolumns
- join selected using(system_table_name,system_table_schema)
- )
- , rpg_ds as (
- select lower(system_column_name) concat ' ' concat rpg_type as rpg_ds_subfield
- from tbl
- order by ordinal_position
- )
- , rpg_ds_long_name as (
- select lower(column_name) concat ' ' concat rpg_type as rpg_ds_subfield
- from tbl
- order by ordinal_position
- )
- , list_short_names as (
- select listagg(lower(trim(system_column_name)),', ')
- within group (order by ordinal_position)
- from tbl
- )
- , list_long_names as (
- select listagg(lower(trim(column_name)),', ')
- within group (order by ordinal_position)
- from tbl
- )
- select * from list_short_names;
|
|