/****************************************************************************** generateCptCntBySTTable.sql Description: Generates a table, CPTCNTBYST, (Concept count by semantic types). The format is the following: ST CPTCNT STDES ==== ====== =================== T001 23 DESCRIPTION OF T001 Time to run: Less than 1 minute. ******************************************************************************/ set serveroutput on drop table CPTCNTBYST; create table CPTCNTBYST(ST varchar2(100), CPTCNT integer, STDES varchar2(500)); declare semanticType varchar2(100); quantityOfConcepts integer; semanticTypeDescription varchar2(500); semanticTypePosition integer; semanticTypeLength integer; subSemanticType char(4); subSemanticTypeDescription varchar2(50); begin for rowSelection in (select INTERTYPE, count(*) as CPTCNT from COMBOS group by INTERTYPE order by CPTCNT) loop semanticType := rowSelection.INTERTYPE; quantityOfConcepts := rowSelection.CPTCNT; -- the following lines concatenate the intersection types; semanticTypePosition := 1; semanticTypeLength :=length(semanticType); semanticTypeDescription := ''; while ( semanticTypePosition < semanticTypeLength ) loop subSemanticType := substr(semanticType, semanticTypePosition, 4); semanticTypePosition := semanticTypePosition + 4; select STY_RL into subSemanticTypeDescription from SRDEF where UI = subSemanticType; semanticTypeDescription := semanticTypeDescription || ' + ' || subSemanticTypeDescription ; end loop; semanticTypeDescription := substr(semanticTypeDescription, 4); insert into CPTCNTBYST values(semanticType, quantityOfConcepts, semanticTypeDescription); -- dbms_output.put_line( semanticType || ' ' || quantityOfConcepts || ' ' || semanticTypeDescription ); end loop; end; /