Home - gregstretton/Transfer GitHub Wiki

SQL Snippets:

ALTER SESSION SET current_schema = EDMATTER_ROOM02; select dst.document_guid, d.state, dst.uploaded_state, dst.is_processed, dst.is_virus_scanned, dst.is_indexed_analytics, dst.IS_CONVERTED_HTML5, dst.is_converted_html5_text, ds.DOCUMENTSET_GUID, ds.NAME, ds.type from document_states dst join LINK_DOCUMENTSETS_DOCUMENTS l on dst.document_guid = l.document_guid join documents d on d.document_guid = l.document_guid join document_sets ds on ds.documentset_guid = l.documentset_guid where ds.TYPE = 'Packet' and l.document_guid in ( select l.DOCUMENT_GUID from LINK_DOCUMENTSETS_DOCUMENTS l join document_sets ds on l.documentset_guid = ds.DOCUMENTSET_GUID where ds.DOCUMENTSET_GUID in (select documentset_guid from document_sets where matter_guid = '7a94f508a6fb4f7f9e97eecf5367a054' and type = 'Packet')) order by ds.name;

select * from EDMATTER_ROOM02.jobs where job_guid = 'ceca1d82f36445fb8f04e1133112383a'

Set UploadJobs to TRANSITIONED:

UPDATE EDMATTER_ROOM01.JOBS SET JOB_STATE = 'TRANSITIONED', MODIFIED_DATE = sysdate where JOB_STATE != 'TRANSITIONED' and MATTER_GUID in ('c3e9bb77818f4f5f8ea697c74526b02f');

Alter Session set current_schema=EDMATTER_ROOM01

(SELECT distinct pr.document_guid FROM pcoding_project_documents PR INNER JOIN pcoding_sample_documents PD ON PD.document_guid=PR.document_guid WHERE pcoding_sample_guid='cb12689bf8b64301b2223cca6be1fdee' AND PD.RESOURCE_STATUS='ACT' AND PR.RESOURCE_STATUS='ACT' and pr.document_guid NOT IN( SELECT distinct pr.document_guid FROM PCODING_RESULTS_TOP_CONFIDENCE PR INNER JOIN pcoding_sample_documents PD on PD.document_guid=PR.document_guid WHERE PR.pcoding_project_guid='bfc9d83ff7b847daba22a74bd2e2e42d' AND pcoding_sample_guid='cb12689bf8b64301b2223cca6be1fdee' AND PD.RESOURCE_STATUS='ACT' AND job_type='CodeAll' AND field_guid='3dfd78dd7f5e4f94a07dc8a77c610023' UNION SELECT distinct FV.document_guid FROM field_values FV INNER JOIN pcoding_sample_documents PD on PD.document_guid=FV.document_guid INNER JOIN PCODING_PROJECT_FIELDS PF ON PF.FIELD_GUID=FV.FIELD_GUID WHERE PF.pcoding_project_guid='bfc9d83ff7b847daba22a74bd2e2e42d' AND pcoding_sample_guid='cb12689bf8b64301b2223cca6be1fdee' AND PD.RESOURCE_STATUS='ACT' AND FV.RESOURCE_STATUS='ACT' AND PF.RESOURCE_STATUS='ACT' AND FV.field_guid='3dfd78dd7f5e4f94a07dc8a77c610023'

))

select * from EDMATTER_ROOM02.jobs where matter_guid = '9b93a2e8bba748c497220a50e11b0540' order by CREATED_DATE desc;

UPDATE EDMATTER_ROOM02.JOBS SET JOB_STATE = 'TRANSITIONED', MODIFIED_DATE = sysdate where JOB_STATE != 'TRANSITIONED' and MATTER_GUID in ('0c264fb227c54048b8e6f680741f2643');

select count(*) from EDMATTER_ROOM02.documents where matter_guid = '9b93a2e8bba748c497220a50e11b0540';

select * from EDMATTER_ROOM01.PCODING_QC_SAMPLES where PCODING_SAMPLE_GUID ='90e60d1479894a80bdf7594d56bc2faf' order by CREATED_DATE desc;

select * from EDMATTER_ROOM01.documents where matter_guid = 'f4fa4c202e43481696d7cf914b67c164';

set serveroutput on; DECLARE counter number; schema_const varchar(50); schema_name varchar(50); row_count number; matter_id varchar(32); sql_stmt varchar(200);

begin counter := 0; schema_const := 'EDMATTER_ROOM0'; row_count := 0; matter_id := 'fffc466b437e4a8989aa0edaef20b00b';

LOOP schema_name := schema_const || to_char(counter); sql_stmt := 'Alter Session set current_schema = ' || schema_name; EXECUTE IMMEDIATE sql_stmt; sql_stmt := 'SELECT COUNT(*) FROM DOCUMENTS WHERE ROWNUM =1 AND matter_guid = :1'; EXECUTE IMMEDIATE sql_stmt INTO row_count USING matter_id; IF row_count > 0 THEN dbms_output.put_line ('Your Schema:'); dbms_output.put_line (schema_name); EXIT; END IF; counter := counter + 1;
IF counter > 9 THEN schema_const := 'EDMATTER_ROOM'; END IF; IF counter > 31 THEN EXIT; END IF; END LOOP; end;

Provider=SQLOLEDB.1;Password=password;Persist Security Info=True;User ID=clxuser;Initial Catalog=CaseLogistix Central;Data Source=qalab22\SQLExpress