/****************************************************************************** generateCombosTable.sql Description: This program creates a single table, COMBOS, which includes all concepts with their pure/intersection semantic types. Time to run: 10 minutes. ******************************************************************************/ set serveroutput on drop table COMBOS; create table COMBOS (CUI CHAR(8), INTERTYPE VARCHAR2(100)); declare previousConcept char(8); previousType varchar2(100); currentConcept char(8); currentType varchar2(100); concatenatedTypes varchar2(100); n integer; begin dbms_output.put_line('Generating COMBOS table......'); n := 0; for rowSelection in (select CUI, TUI from MRSTY order by CUI, TUI) loop if n = 0 then previousConcept := rowSelection.CUI; previousType := rowSelection.TUI; concatenatedTypes := previousType; n := 1; else currentConcept := rowSelection.CUI; currentType := rowSelection.TUI; if currentConcept = previousConcept then concatenatedTypes := concatenatedTypes || currentType; previousType := currentType; else --dbms_output.put_line(previousConcept || ' ' || concatenatedTypes); insert into COMBOS values(previousConcept, concatenatedTypes); previousConcept := currentConcept; concatenatedTypes := currentType; end if; end if; end loop; dbms_output.put_line('completed.'); --dbms_output.put_line(previousConcept || ' ' || concatenatedTypes); insert into COMBOS values(previousConcept, concatenatedTypes); end; /