 END;
 /
 
-insert into evidence(pubmed_id, sentence) select distinct pubmed_id, 'Pathway publication' from zstg_pid_interactionreference minus select distinct pubmed_id, to_char(dbms_lob.substr(sentence, 1, 4000)) from evidence;
+--insert into evidence(pubmed_id, sentence) select distinct pubmed_id, 'Pathway publication' from zstg_pid_interactionreference minus select distinct pubmed_id, to_char(dbms_lob.substr(sentence, 1, 4000)) from evidence;
+
+insert into evidence(pubmed_id, sentence)
+select distinct pubmed_id, 'Pathway publication' from zstg_pid_reference_pubmed minus select distinct pubmed_id, to_char(dbms_lob.substr(sentence, 50,1)) from evidence;
 commit;
 
 SELECT MAX(ID) + 1 AS V_MAXROW

/

-- inserted rows..but the id does not help find any associated interactions..so what are these conditions for?
insert into pid_participant(condition_name, discriminator, pid_interaction_id) select distinct conditionname,'Condition' as discriminator, interaction_id from zstg_pid_interactioncondition;
commit;

-- Added on the basis of the May 09 dump
update pid_participant p set p.CONDITION_NAME = (select distinct conditionname from zstg_pid_interactioncondition x 
where p.PID_PHYSICALENTITY_ID = x.INTERACTION_ID) where condition_name is null and p.discriminator = 'Condition';
commit;




commit;

--insert into pid_interaction_evidence(interaction_id, evidence_id) select distinct i.id, e.id from pid_interaction i, evidence e, zstg_pid_interactionreference z where z.interaction_id = i.pid_interaction_id and z.pubmed_id=e.pubmed_id and to_char(substr(e.sentence, 1, 4000))='Pathway publication';


insert into pid_interaction_evidence(interaction_id, evidence_id) select distinct i.id, e.id from pid_interaction i, evidence e, zstg_pid_interactionreference z, zstg_pid_reference_pubmed z2 where z.interaction_id = i.pid_interaction_id and z.pubmed_id= z2.reference_id and z2.pubmed_id = e.pubmed_id and to_char(substr(e.sentence, 1, 4000))='Pathway publication';

commit;

-- load into pid_entityName and pid_entityAccession
