 
 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 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


-- inserting pathway conditions here also
-- why?
insert into pid_participant(id, discriminator, pid_interaction_id, pid_physicalentity_id, pid_complex_component_order) select phys_part_id.nextval, decode(role, 'input', 'Input', 'output', 'Output', 'positivecontrol', 'PositiveControl', 'negativecontrol', 'NegativeControl', 'condition', 'Condition'),interaction_id, physicalentity_id, order_of_interactants from zstg_pid_interactants;
commit;

update zstg_pid_complex_component z set z.complex_id = (select x.id from pid_physical_entity x where z.pid_complex_id = x.PID_PHYSICALENTITY_ID);

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_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
