<commit_before>/**************************************************************************
* Copyright 2016 Observational Health Data Sciences and Informatics (OHDSI)
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
* 
* Authors: <NAME>, <NAME>, <NAME>
* Date: 2017
**************************************************************************/
SELECT error_type, COUNT(1)
FROM (SELECT drug_concept_id,'Empty strength' AS error_type
      FROM drug_strength
      WHERE NVL(amount_value,numerator_value) IS NULL
UNION
      SELECT drug_concept_id, 'missing unit'
      FROM drug_strength
      WHERE (numerator_value IS NOT NULL AND numerator_unit_concept_id IS NULL)
      OR    (denominator_value IS NOT NULL AND denominator_unit_concept_id IS NULL)
      OR    (amount_value IS NOT NULL AND amount_unit_concept_id IS NULL)
UNION
      SELECT drug_concept_id,'Impossible dosages'
      FROM drug_strength
      WHERE (NUMERATOR_UNIT_CONCEPT_ID = 8576 AND DENOMINATOR_UNIT_CONCEPT_ID = 8587 AND NUMERATOR_VALUE / DENOMINATOR_VALUE > 1000)
      OR    (NUMERATOR_UNIT_CONCEPT_ID = 8576 AND DENOMINATOR_UNIT_CONCEPT_ID = 8576 AND NUMERATOR_VALUE / DENOMINATOR_VALUE > 1)
UNION
      SELECT drug_concept_id, 'Percents in wrong place or have denominator'
      FROM drug_strength
      WHERE (NUMERATOR_UNIT_CONCEPT_ID = 8554 AND DENOMINATOR_UNIT_CONCEPT_ID IS NOT NULL)
      OR    AMOUNT_UNIT_CONCEPT_ID = 8554
UNION
      SELECT an_id, 'wrong ancestor RxE to descedant RxNorm'
      FROM (SELECT a.min_levels_of_separation AS a_min, an.concept_id AS an_id, an.concept_name AS an_name,an.vocabulary_id AS an_vocab,an.domain_id AS an_domain, an.concept_class_id AS an_class,de.concept_id AS de_id,de.concept_name AS de_name,de.vocabulary_id AS de_vocab,de.domain_id AS de_domain, de.concept_class_id AS de_class
            FROM concept an
              JOIN concept_ancestor a ON a.ancestor_concept_id = an.concept_id AND an.vocabulary_id = 'RxNorm Extension'
              JOIN concept de ON de.concept_id = a.descendant_concept_id  AND de.vocabulary_id = 'RxNorm')
UNION
      SELECT concept_id,'Packs missing in pack content'
      FROM concept
      WHERE (concept_class_id LIKE '%Pack%' OR (concept_class_id = 'Marketed Product' AND concept_name LIKE '%}% Pack %'))
      AND   vocabulary_id = 'RxNorm Extension'
      AND   invalid_reason IS NULL
      AND   concept_id NOT IN (SELECT pack_concept_id FROM pack_content)
UNION<commit_msg>Add 'Ml' to QA<commit_after>/**************************************************************************
* Copyright 2016 Observational Health Data Sciences and Informatics (OHDSI)
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
* 
* Authors: <NAME>, <NAME>, <NAME>
* Date: 2017
**************************************************************************/
SELECT error_type, COUNT(1)
FROM (SELECT drug_concept_id,'Empty strength' AS error_type
      FROM drug_strength
      WHERE NVL(amount_value,numerator_value) IS NULL
UNION
      SELECT drug_concept_id, 'missing unit'
      FROM drug_strength
      WHERE (numerator_value IS NOT NULL AND numerator_unit_concept_id IS NULL)
      OR    (denominator_value IS NOT NULL AND denominator_unit_concept_id IS NULL)
      OR    (amount_value IS NOT NULL AND amount_unit_concept_id IS NULL)
UNION
      SELECT drug_concept_id,'Impossible dosages'
      FROM drug_strength
      WHERE (NUMERATOR_UNIT_CONCEPT_ID = 8576 AND DENOMINATOR_UNIT_CONCEPT_ID = 8587 AND NUMERATOR_VALUE / DENOMINATOR_VALUE > 1000)
      OR    (NUMERATOR_UNIT_CONCEPT_ID = 8576 AND DENOMINATOR_UNIT_CONCEPT_ID = 8576 AND NUMERATOR_VALUE / DENOMINATOR_VALUE > 1)
UNION
      SELECT drug_concept_id, 'Percents in wrong place or have denominator'
      FROM drug_strength
      WHERE (NUMERATOR_UNIT_CONCEPT_ID = 8554 AND DENOMINATOR_UNIT_CONCEPT_ID IS NOT NULL)
      OR    AMOUNT_UNIT_CONCEPT_ID = 8554
UNION
      SELECT concept_id, 'solid forms have denominator'
      FROM concept
      JOIN drug_strength
      ON drug_concept_id = concept_id  AND (concept_name LIKE '%Tablet%'  OR concept_name LIKE '%Capsule%'  OR concept_name LIKE '%Lozenge%' OR concept_name LIKE '%Pellet%')-- solid forms defined by their forms 
      AND numerator_value IS NOT NULL
UNION
      SELECT concept_id, 'ML in amount/numerator'
      FROM concept
      JOIN drug_strength ON drug_concept_id = concept_id
      WHERE numerator_unit_concept_id = 8587 OR amount_unit_concept_id = 8587
UNION
      SELECT an_id, 'wrong ancestor RxE to descedant RxNorm'
      FROM (SELECT a.min_levels_of_separation AS a_min, an.concept_id AS an_id, an.concept_name AS an_name,an.vocabulary_id AS an_vocab,an.domain_id AS an_domain, an.concept_class_id AS an_class,de.concept_id AS de_id,de.concept_name AS de_name,de.vocabulary_id AS de_vocab,de.domain_id AS de_domain, de.concept_class_id AS de_class
            FROM concept an
              JOIN concept_ancestor a ON a.ancestor_concept_id = an.concept_id AND an.vocabulary_id = 'RxNorm Extension'
              JOIN concept de ON de.concept_id = a.descendant_concept_id  AND de.vocabulary_id = 'RxNorm')
UNION
      SELECT concept_id,'Packs missing in pack content'
      FROM concept
      WHERE (concept_class_id LIKE '%Pack%' OR (concept_class_id = 'Marketed Product' AND concept_name LIKE '%}% Pack %'))
      AND   vocabulary_id = 'RxNorm Extension'
      AND   invalid_reason IS NULL
      AND   concept_id NOT IN (SELECT pack_concept_id FROM pack_content)
UNION
{'fraction_non_alphanumeric': 0.10185041206655264, 'fraction_numerical': 0.014305706733011974, 'mean_word_length': 4.191283292978208, 'pattern_counts': {'":': 0, '<': 9, '<?xml version=': 0, '>': 13, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '941040', 'n_tokens_mistral': 2088, 'n_tokens_neox': 1894, 'n_words': 642}