What I wanted, was to be able to extract, using a SQL query against the designer repository, the “Description” attribute from our Tables so that I could create table comments in the database which had the description from the table in Designer.
Extracting scalar attributes is simple enough, however, description is a multi row text property so it needed a bit more thought…and rather than reinvent the wheel, I had a look at Lucas’ stuff and sure enough found the post above. It was almost what I wanted, only I had to change it to look for table stuff rather than entities and attributes.
I used the same TYPE and sum_string_table function as Lucas so if you’re trying to use this stuff you’ll probably need to read Lucas’ article first.
The query I ended up with is below…it’s been “sanitized” somewhat, but I’m sure you’d get the picture, if retrieving stuff out of the designer repository is a requirement of yours.
WITH dsc AS ( SELECT txt.txt_text , txt.txt_ref FROM cdi_text txt WHERE txt.txt_type = 'CDIDSC' ) , add_cast AS ( SELECT appsys.name application_system_name , b.name table_name , b.alias , CAST(COLLECT(dsc.txt_text) AS string_table) tab_description FROM dsc , designer.ci_application_systems appsys , designer.ci_app_sys_tables a , designer.ci_table_definitions b WHERE dsc.txt_ref = a.table_reference AND b.irid = a.table_reference AND a.parent_ivid = appsys.ivid GROUP BY appsys.name , b.name , b.alias ) SELECT application_system_name , table_name , alias , sum_string_table(tab_description) FROM add_cast WHERE application_system_name = 'MY_APP_SYS' and table_name = 'MY_TABLE_NAME' /
On another note, regular visitors may realise I’ve now got my own oramoss dot com domain and that my blogger blog is published on that domain now.
Thanks to Andreas Viklund for the template.
If anyone sees anything untoward with the new site please feel free to drop me a note. It’s a bit thin on content but I’ll work on that over time.