 ) WHERE rn = 1 AND ACTIVE = 1
 ;
 
-commit;
-
-DELETE                                                    
-      FROM  CONCEPT_RELATIONSHIP_STAGE r
-      WHERE 1 = 1 AND r.RELATIONSHIP_ID = 010
-            AND ROWID <>
-                   (SELECT MIN (t.ROWID)
-                      FROM CONCEPT_RELATIONSHIP_STAGE t
-                     WHERE t.RELATIONSHIP_ID = 010
-                           AND (t.CONCEPT_ID_1 = r.CONCEPT_ID_1
-                                AND t.CONCEPT_ID_2 = r.CONCEPT_ID_2
-                                OR t.CONCEPT_ID_2 = r.CONCEPT_ID_1
-                                   AND t.CONCEPT_ID_1 = r.CONCEPT_ID_2))
-
-commit;
+commit;
+
+DELETE                                                    
+      FROM  CONCEPT_RELATIONSHIP_STAGE r
+      WHERE 1 = 1 AND r.RELATIONSHIP_ID = 010 AND ROWID <> (
+        SELECT MIN (t.ROWID) 
+        FROM CONCEPT_RELATIONSHIP_STAGE t   
+        WHERE 
+    	    t.RELATIONSHIP_ID = 010 AND (
+    		t.CONCEPT_ID_1 = r.CONCEPT_ID_1 AND t.CONCEPT_ID_2 = r.CONCEPT_ID_2 OR 
+    		t.CONCEPT_ID_2 = r.CONCEPT_ID_1 AND t.CONCEPT_ID_1 = r.CONCEPT_ID_2
+    	    )
+      );
+commit;
 
 SELECT 'Revers-RELATIONS' FROM dual;
 INSERT INTO CONCEPT_RELATIONSHIP_STAGE(

    DEV.RELATIONSHIP rt WHERE rt.RELATIONSHIP_ID = r.RELATIONSHIP_ID), 998)
  FROM  CONCEPT_RELATIONSHIP_STAGE r
  ; 
commit;

DROP TABLE TMP_ROW_REL;

exit;

/******************************************************************************
*
*  OMOP - Cloud Research Lab
*
*  Observational Medical Outcomes Partnership
*  (c) Foundation for the National Institutes of Health (FNIH)
*
*  Licensed under the Apache License, Version 2.0 (the "License"); you may not
*  use this file except in compliance with the License. You may obtain a copy
*  of the License at http://omop.fnih.org/publiclicense.
*
*  Unless required by applicable law or agreed to in writing, software
*  distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
*  WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. Any
*  redistributions of this work or any derivative work or modification based on
*  this work should be accompanied by the following source attribution: "This
*  work is based on work by the Observational Medical Outcomes Partnership
*  (OMOP) and used under license from the FNIH at
*  http://omop.fnih.org/publiclicense.
*
*  Any scientific publication that is based on this work should include a
*  reference to http://omop.fnih.org.
*
*  Date:           2021/07/06
*
*  Load new concept relationships stage, identify invalid codes  information 
*  Loaded from the raw staged data SCT2_RELA_FULL_INT into the staged data CONCEPT_RELATIONSHIP_STAGE, identify invalid code information    
*     
*  Usage: 
*  echo "EXIT" | sqlplus SNOMED_20120131/myPass@DEV_VOCAB @01_transform_row_relations.sql 
*
******************************************************************************/
SPOOL 01_transform_row_relations.log

TRUNCATE TABLE CONCEPT_RELATIONSHIP_STAGE; 

SELECT '01-RELATIONS' FROM dual;

-- get raw relations from source
DROP TABLE TMP_ROW_REL;
CREATE TABLE TMP_ROW_REL AS
SELECT SOURCEID, DESTINATIONID, TERM FROM (
SELECT -- + INDEX(d)
    r.SOURCEID, r.DESTINATIONID, d.TERM
, ROW_NUMBER() OVER (PARTITION BY  r.ID ORDER BY TO_DATE(r.EFFECTIVETIME,'YYYYMMDD') DESC
       ) rn    
       , r.ACTIVE 
          FROM SCT2_RELA_FULL_MERGED r,
               sct2_Desc_Full_MERGED d
         WHERE 1 = 1 AND r.TYPEID = d.CONCEPTID
--         AND D.MODULEID = R.MODULEID
         AND d.TERM NOT LIKE '%(attribute)%'
         AND NOT 
         (
         (r.SOURCEID = 243796009   AND r.DESTINATIONID = 138875005) OR
         (r.SOURCEID = 246061005   AND r.DESTINATIONID = 138875005) OR
         (r.SOURCEID = 246188002   AND r.DESTINATIONID = 138875005) OR
         (r.SOURCEID = 64572001    AND r.DESTINATIONID = 138875005) OR
         (r.SOURCEID = 900000000000441003   AND r.DESTINATIONID = 138875005)
         )
)
WHERE  rn = 1 AND active = 1; 

-- set relationship_id
DROP TABLE TMP_REL_TYPE;
CREATE TABLE TMP_REL_TYPE AS SELECT 
    SOURCEID, 
    DESTINATIONID,
       CASE
          WHEN TERM = 'Is a' THEN 010
          WHEN TERM = 'Recipient category' THEN 029
          WHEN TERM = 'Procedure site' THEN 030
          WHEN TERM = 'Priority' THEN 031
          WHEN TERM = 'Pathological process' THEN 032
          WHEN TERM = 'Part of' THEN 033
          WHEN TERM = 'Severity' THEN 034
          WHEN TERM = 'Revision status' THEN 035
          WHEN TERM = 'Access' THEN 036
          WHEN TERM = 'Occurrence' THEN 037
          WHEN TERM = 'Method' THEN 038
          WHEN TERM = 'Laterality' THEN 039
          WHEN TERM = 'Interprets' THEN 040
          WHEN TERM = 'Indirect morphology' THEN 041
          WHEN TERM = 'Indirect device' THEN 042
          WHEN TERM = 'Has specimen' THEN 043
          WHEN TERM = 'Has interpretation' THEN 044
          WHEN TERM = 'Has intent' THEN 045
          WHEN TERM = 'Has focus' THEN 046
          WHEN TERM = 'Has definitional manifestation' THEN 047
          WHEN TERM = 'Has active ingredient' THEN 048
          WHEN TERM = 'Finding site' THEN 049
          WHEN TERM = 'Episodicity' THEN 050
          WHEN TERM = 'Direct substance' THEN 051
          WHEN TERM = 'Direct morphology' THEN 052
          WHEN TERM = 'Direct device' THEN 053
          WHEN TERM = 'Component' THEN 054
          WHEN TERM = 'Causative agent' THEN 055
          WHEN TERM = 'Associated morphology' THEN 056
          WHEN TERM = 'Associated finding' THEN 057
          WHEN TERM = 'Measurement Method' THEN 058
          WHEN TERM = 'Property' THEN 059
          WHEN TERM = 'Scale type' THEN 060
          WHEN TERM = 'Time aspect' THEN 061
          WHEN TERM = 'Specimen procedure' THEN 062
          WHEN TERM = 'Specimen source identity' THEN 063
          WHEN TERM = 'Specimen source morphology' THEN 064
          WHEN TERM = 'Specimen source topography' THEN 065
          WHEN TERM = 'Specimen substance' THEN 066
          WHEN TERM = 'Due to' THEN 067
          WHEN TERM = 'Subject TERMtionship context' THEN 068
          WHEN TERM = 'Has dose form' THEN 069
          WHEN TERM = 'After' THEN 070
          WHEN TERM = 'Associated procedure' THEN 071
          WHEN TERM = 'Procedure site - Direct' THEN 072
          WHEN TERM = 'Procedure site - Indirect' THEN 073
          WHEN TERM = 'Procedure device' THEN 074
          WHEN TERM = 'Procedure morphology' THEN 075
          WHEN TERM = 'Finding context' THEN 076
          WHEN TERM = 'Procedure context' THEN 077
          WHEN TERM = 'Temporal context' THEN 078
          WHEN TERM = 'Associated with' THEN 079
          WHEN TERM = 'Surgical approach' THEN 080
          WHEN TERM = 'Using device' THEN 081
          WHEN TERM = 'Using energy' THEN 082
          WHEN TERM = 'Using substance' THEN 083
          WHEN TERM = 'Using access device' THEN 084
          WHEN TERM = 'Clinical course' THEN 085
          WHEN TERM = 'Route of administration' THEN 086
          WHEN TERM = 'Finding method' THEN 087
          WHEN TERM = 'Finding informer' THEN 088
          ELSE 999
       END
          AS RELATIONSHIP_ID
  FROM TMP_ROW_REL;

-- change direction of 010 (Is a) relations
DROP TABLE TMP_REL_DIR;
CREATE TABLE TMP_REL_DIR AS SELECT 
    CASE WHEN RELATIONSHIP_ID = 010 THEN DESTINATIONID ELSE SOURCEID END AS SOURCEID,
    CASE WHEN RELATIONSHIP_ID = 010 THEN SOURCEID ELSE DESTINATIONID END AS DESTINATIONID,
    RELATIONSHIP_ID AS RELATIONSHIP_ID
    FROM TMP_REL_TYPE;

-- convert source codes to concept ids
DROP TABLE TMP_REL_IDS;
CREATE TABLE TMP_REL_IDS AS 
    SELECT
	s1.CONCEPT_ID AS CONCEPT_ID_1,
        s2.CONCEPT_ID AS CONCEPT_ID_2,
	r.RELATIONSHIP_ID AS RELATIONSHIP_ID
    FROM TMP_REL_DIR r, DEV.CONCEPT s1, DEV.CONCEPT s2 
    WHERE 
	r.SOURCEID = s1.CONCEPT_CODE AND s1.VOCABULARY_ID = 1 AND
        r.DESTINATIONID = s2.CONCEPT_CODE AND s2.VOCABULARY_ID = 1;
                                
-- add results to stage
INSERT INTO CONCEPT_RELATIONSHIP_STAGE(CONCEPT_ID_1, CONCEPT_ID_2, RELATIONSHIP_ID)
SELECT DISTINCT CONCEPT_ID_1, CONCEPT_ID_2, RELATIONSHIP_ID
FROM TMP_REL_IDS
WHERE CONCEPT_ID_1 IS NOT NULL AND CONCEPT_ID_2 IS NOT NULL;

COMMIT;

INSERT INTO CONCEPT_RELATIONSHIP_STAGE(CONCEPT_ID_1, CONCEPT_ID_2, RELATIONSHIP_ID)
SELECT CONCEPT_ID_1,    CONCEPT_ID_2,    RELATIONSHIP_ID FROM (
