 WHERE c.invalid_reason='D' and relationship_id in ('Maps to','Replaced by')) 
 AND invalid_reason='D'
 
-union
+UNION
 
 --check if relationship 'Has standard brand' exsists only to brand names
 select concept_code_1,'Has standard brand refers not to brand name'
 from concept_relationship_stage crs JOIN devv5.concept c on concept_code_2=concept_code
 WHERE relationship_id='Has standard brand' AND c.concept_class_id!='Brand Name' AND c.vocabulary_id='RxNorm'
 
-union
+UNION
 --check if relationship 'Has standard form' exsists only to dose form;
 select concept_code_1,'Has standard form refers not to dose form'
 from concept_relationship_stage crs JOIN devv5.concept c on concept_code_2=concept_code
 WHERE relationship_id='Has standard form' AND c.concept_class_id!='Dose Form' AND c.vocabulary_id='RxNorm'
 
-union
+UNION
 
 --check if relationship 'Has standard ing' exsists only to ingredient
 select concept_code_1,'Has standard ing refers not to ingredient'
 from concept_relationship_stage crs JOIN devv5.concept c on concept_code_2=concept_code
 WHERE relationship_id='Has standard ing' AND c.concept_class_id!='Ingredient' AND c.vocabulary_id='RxNorm'
 
-union
+UNION
 
 select distinct concept_code_1, 'Has tradename refers to wrong concept_class' 
 from concept_relationship_stage crs 

WHERE relationship_id='Constitutes' AND (cs.concept_class_id not like '%Drug%'
OR cs2.concept_class_id not like '%Comp%')

union

--There should be no standard deprecated and updated concepts
select distinct c.concept_code, 'Wrong standard_concept' 
from concept_stage c JOIN concept_relationship_stage cr ON cr.concept_code_1=c.concept_code 
WHERE c.standard_concept='S' and (c.invalid_reason='U' or c.invalid_reason='D')
union
select distinct c.concept_code, 'Wrong standard_concept'
from concept_stage c JOIN concept_relationship_stage cr ON cr.concept_code_2=c.concept_code 
WHERE c.standard_concept='S' and (c.invalid_reason='U' or c.invalid_reason='D')

union

--Improper valid_end_date
select concept_code, 'Improper valid_end_date' from concept_stage where concept_code not in (
select concept_code  from concept_stage
where  valid_end_date <=SYSDATE or valid_end_date = to_date ('2099-12-31', 'YYYY-MM-DD') )
union
--Improper valid_start_date
select concept_code, 'Improper valid_start_date' from concept_stage where valid_start_date > SYSDATE
union
-- duplicated Attributes
with new_concept as (select * from  concept union  select * from  concept_stage), 
new_concept_relat as (
select CONCEPT_ID_1,CONCEPT_ID_2,CONCEPT_CODE_1,CONCEPT_CODE_2,VOCABULARY_ID_1,VOCABULARY_ID_2,RELATIONSHIP_ID,VALID_START_DATE,VALID_END_DATE,INVALID_REASON from concept_relationship_stage
union
select CONCEPT_ID_1,CONCEPT_ID_2,CONCEPT_CODE_2,CONCEPT_CODE_1,VOCABULARY_ID_1,VOCABULARY_ID_2,'reverse '||RELATIONSHIP_ID as RELATIONSHIP_ID,VALID_START_DATE,VALID_END_DATE,INVALID_REASON from concept_relationship_stage
)
 select  *
  from new_concept_relat r
join new_concept b on concept_code_1 = b.concept_code
join new_concept c on concept_code_2 = c.concept_code
where c.concept_class_id in ('Dose Form', 'Brand Name', 'Supplier')
and b.vocabulary_id like 'RxNorm%' and c.vocabulary_id like 'RxNorm%' and r.invalid_reason is null and (b.concept_class_id like '%Drug%' or b.concept_class_id like '%Marketed%' or  b.concept_class_id like '%Box%' ) --And relationship_id = 'RxNorm has dose form'
group by concept_code_1, c.concept_class_id having count (1) >1 
union
select 
select concept_name from concept_stage where vocabulary_id = 'RxNorm Extension' and concept_name not like '%...%' group by concept_name , invalid_reason 
 having count (1) > 1
)
group by error_type
;

WHERE relationship_id='Has tradename' AND (cs.concept_class_id not like '%Branded%'
OR cs2.concept_class_id not like '%Clinical%')

UNION

--Should be only Component to Drug relationship
select distinct concept_code_1, 'Constitutes refers to wrong concept_class'
