combine two queries

[igorkou]

Can you ask someone how I can combine two queries below. Currently,
when I
am running them seporate I am getting the right result, but when I am
combining them I am getting 0.


select count (distinct discrepancy_entry_id)
from discrepancy_entries d, procedure_details p, procedures c where
d.procedure_id= p. procedure_id and d.procedure_id=c. procedure_id and
d.procedure_detail_id=p. procedure_detail_id and d.clinical_study_id =
p.clinical_study_id and d.clinical_study_id = c.clinical_study_id and
d.clinical_study_id ='20446' and d.de_sub_type_code = 'MULTIVARIATE'
and
d.DISCREPANCY_REV_STATUS_CODE<>'TMS EVALUATION' and
(d.DISCREPANCY_RESOLU_TYPE_CODE NOT in ('ALPHA CODE','DATA REMOVED',

'DT CREATED','DVG','THESAURUS','VTA CREATED')
OR d.DISCREPANCY_RESOLU_TYPE_CODE is NULL)
and c.PRO_SUB_TYPE_CODE <>'DERIVATION';

select count (distinct discrepancy_entry_id)
from discrepancy_entries d, procedure_details p, procedures c where
d.procedure_id= p. procedure_id and d.procedure_id=c. procedure_id and
d.procedure_detail_id=p. procedure_detail_id and d.clinical_study_id =
p.clinical_study_id and d.clinical_study_id = c.clinical_study_id and
d.clinical_study_id ='20446' and d.DE_SUB_TYPE_CODE='UNIVARIATE' and
d.DISCREPANCY_TYPE_CODE='MANDATORY'
and c.PRO_SUB_TYPE_CODE <>'DERIVATION';

[leprechaun]

Can you ask someone how I can combine two queries below. Currently,
when I
am running them seporate I am getting the right result, but when I am
combining them I am getting 0.

select count (distinct discrepancy_entry_id)
from discrepancy_entries d, procedure_details p, procedures c where
d.procedure_id= p. procedure_id and d.procedure_id=c. procedure_id and
d.procedure_detail_id=p. procedure_detail_id and d.clinical_study_id =
p.clinical_study_id and d.clinical_study_id = c.clinical_study_id and
d.clinical_study_id ='20446' and d.de_sub_type_code = 'MULTIVARIATE'
and
d.DISCREPANCY_REV_STATUS_CODE<>'TMS EVALUATION' and
(d.DISCREPANCY_RESOLU_TYPE_CODE NOT in ('ALPHA CODE','DATA REMOVED',

'DT CREATED','DVG','THESAURUS','VTA CREATED')
OR d.DISCREPANCY_RESOLU_TYPE_CODE is NULL)
and c.PRO_SUB_TYPE_CODE <>'DERIVATION';

select count (distinct discrepancy_entry_id)
from discrepancy_entries d, procedure_details p, procedures c where
d.procedure_id= p. procedure_id and d.procedure_id=c. procedure_id and
d.procedure_detail_id=p. procedure_detail_id and d.clinical_study_id =
p.clinical_study_id and d.clinical_study_id = c.clinical_study_id and
d.clinical_study_id ='20446' and d.DE_SUB_TYPE_CODE='UNIVARIATE' and
d.DISCREPANCY_TYPE_CODE='MANDATORY'
and c.PRO_SUB_TYPE_CODE <>'DERIVATION';

How do you combine them?

Code: Select all

SELECT COUNT (DISTINCT discrepancy_entry_id) 
FROM discrepancy_entries d, procedure_details p, procedures c WHERE 
d.procedure_id= p. procedure_id AND d.procedure_id=c. procedure_id AND 
d.procedure_detail_id=p. procedure_detail_id AND d.clinical_study_id = 
p.clinical_study_id AND d.clinical_study_id = c.clinical_study_id AND 
d.clinical_study_id ='20446' AND 

((
d.DE_SUB_TYPE_CODE='UNIVARIATE' AND 
d.DISCREPANCY_TYPE_CODE='MANDATORY'
) OR (
d.de_sub_type_code = 'MULTIVARIATE' AND 
d.DISCREPANCY_REV_STATUS_CODE<>'TMS EVALUATION' AND 
(d.DISCREPANCY_RESOLU_TYPE_CODE NOT in 
   ('ALPHA CODE','DATA REMOVED', 'DT CREATED','DVG','THESAURUS','VTA CREATED') OR d.DISCREPANCY_RESOLU_TYPE_CODE is NULL) 
))

AND c.PRO_SUB_TYPE_CODE <>'DERIVATION'; 

Something like that should work. But the result won't necessarily be the sum of the two separate queries as the IDs may overlap.