©Mike Moxcey 2002
 

E/R Queries

This section contains queries against the tables in Designer that apply to the ERD tool.  I used these instead of trying to modify the ckentdef report.  For fancier reports, modify the ckentdef.rdf and other reports under the Entity/Relationship Modelling [sic] section of the Repository Reports.
 
Containers

Entities

Entities and Attributes

Entities, Attributes, and Domains

Domains with Values

Attributes with Values




REM DES_CONTAINER.SQL by Mike Moxcey
REM ===========================================================
REM This query gets you the list of containers in Designer
REM Copy the container you want into the other queries
REM ===========================================================

column name format A40 trunc

select id,name from ci_container_elements
/

This will output all names of all containers, even subcontainers.  You can do something fancy to get the hierarchy if you need (but I’d just use the report then).  Output looks like this:

       ID NAME
--------- -----------------
1.773E+36 SYSTEM FOLDER
7.302E+35 TUTORIAL
7.341E+35 Moxcey
7.415E+35 MIS2000 (6)

Copy the NAME you need exactly as it appears for the other queries.



 

REM DES_ENT.SQL by Mike Moxcey
REM ====================================================
REM This gets the list of entities based on a container name
REM You can get the container name by running DES_CONTAINER.SQL
REM ===================================================

select ENT.name, ENT.short_name
from ci_folder_members APPENT, ci_entities ENT
where APPENT.member_object=ENT.id
and APPENT.folder_reference in
 (select id from ci_container_elements
  where name = 'MIS2000 (6)'
 )
order by name
/

You can see how I copy&pasted the container name directly into the query.  There are probably ways to do this differently, but I'm only ever working in one container at a time so this is quick enough.
 

Sample output:

SQL> start des_ent

NAME                 SHORT_NAME
-------------------- ----------
ACTIVITY             ACT
ACTIVITY UOM         ACTUOM
ADDRESS              ADDR
ADDRESS CONTACT TYPE ADDCONTTYP



 

rem DES_ENTATT.SQL by Mike Moxcey
rem ==================================================
rem To get Entity Names and Attributes for a quick and dirty report
rem ==================================================

break on entity skip 1
column entity format A20 trunc
column attribute format a20 trunc

set pagesize 60 linesize 60
spool des_entatt.lst

select ENT.name Entity, ATT.name Attribute, ATT.format
from ci_folder_members APPENT, ci_entities ENT, ci_attributes ATT
where APPENT.member_object=ENT.id
and ENT.id = ATT.entity_reference
and APPENT.folder_reference in
 (select id from ci_container_elements
  where name = 'MIS2000 (6)'
 )
order by ENT.name, ATT.name
/

spool off
 

Sample output

SQL> start des_entatt

ENTITY               ATTRIBUTE            FORMAT
-------------------- -------------------- ----------
ACTIVITY             ABBR                 VARCHAR2
                     CLASS                VARCHAR2
                     ID                   NUMBER
                     NAME                 VARCHAR2
                     REMARKS              VARCHAR2

ADDRESS              DIRECTIONS           VARCHAR2
                     ID                   NUMBER
                     LINE 1               VARCHAR2
                     LINE 2               VARCHAR2
                     LINE 3               VARCHAR2
                     POSTAL CODE          NUMBER
 


rem DES_ENTATTDOM.SQL by Mike Moxcey
rem ======================================================
rem To get Entity Names, Attributes,
rem     and Domains for a quick and dirty report
rem ======================================================

break on entity skip 1
column entity format A20 trunc
column attribute format a20 trunc
column domain like entity

set pagesize 60 linesize 60
spool des_entattdom.lst

select ENT.name Entity, ATT.name Attribute, ATT.format, DOM.name Domain
from ci_folder_members APPENT, ci_entities ENT,
     ci_attributes ATT, ci_domains DOM
where APPENT.member_object=ENT.id
and ENT.id = ATT.entity_reference
and ATT. domain_reference = DOM.id(+)
and APPENT.folder_reference in
 (select id from ci_container_elements
  where name = 'MIS2000 (6)'
 )
order by ENT.name, ATT.name
/

spool off

Sample Output.  It shows those that don’t have domains, too.

EMPLOYEE WORK ROLE   ABBR                 VARCHAR2   ABBREVIATION
                     ID                   NUMBER     ID_NR
                     NAME                 VARCHAR2   NAME
                     REMARKS              VARCHAR2   REMARKS

GEOGRAPHIC AREA      GSA CITY CODE        VARCHAR2
                     GSA CITY NAME        VARCHAR2
                     GSA COUNTY CODE      VARCHAR2
                     GSA COUNTY NAME      VARCHAR2
                     GSA LOCATION CODE    VARCHAR2
                     GSA STATE ABBREV     CHAR       STATE_CODE
                     GSA STATE CODE       VARCHAR2
                     GSA STATE NAME       VARCHAR2
                     ID                   NUMBER     ID_NR


rem DES_DOMVALS.SQL by Mike Moxcey
rem ======================================================
rem To get Domains that have values in them
rem ======================================================
 

set pagesize 60 linesize 60
spool des_domvals.lst

ttitle left 'Domains with Values' skip 2

column domain format A15 trunc
column attribute like domain
column low_value like domain
column abbreviation like domain

break on domain on attribute

select dom.name Domain, att.name attribute,  atv.low_value, atv.abbreviation
from ci_attribute_values atv, ci_domains DOM,
     ci_folder_members APPENT, ci_entities ENT,
     ci_attributes ATT
where APPENT.member_object=ENT.id
and ENT.id = ATT.entity_reference
and ATT.domain_reference = DOM.id
and atv.domain_reference= DOM.id
and APPENT.folder_reference in
 (select id from ci_container_elements
  where name = 'MIS2000 (6)'
 )
order by 1, 2
/

spool off

Sample Output.

Domains with Values

DOMAIN          ATTRIBUTE       LOW_VALUE       ABBREVIATION
--------------- --------------- --------------- ---------------
LKP_LANDCLASS   ABBR            Private Land    PRIV
                                BLM Land        BLM
                                Forest Service  FS
                                Indian Land     BIA
                                Other Public La PUB


rem DES_ATTVALS.SQL by Mike Moxcey
rem ======================================================
rem To get Attributes that have values in them
rem ======================================================
 

set pagesize 60 linesize 60
spool des_attvals.lst

column name format A20
column low_value like name

ttitle left 'Domains with Values' skip 2

select ENT.name, ATT.name, ATTVAL.abbreviation, ATTVAL.low_value
from ci_attribute_values ATTVAL, ci_attributes ATT,
     ci_folder_members APPENT, ci_entities ENT
where attribute_reference = ATT.id
and APPENT.member_object=ENT.id
and ENT.id = ATT.entity_reference
and APPENT.folder_reference in
 (select id from ci_container_elements
  where name = 'MIS2000 (6)'
 )
/

spool off

No sample output because I've cleaned it up and have put all values into domains, not in attributes.



home