Definition

Purpose

The drug_code model exposes drug coding metadata with EMIS and DMD references from the anonymised extract.

Information

This master table provides drug code definitions and cross-references to external terminology systems. Each drug code is linked to its DMD (Dictionary of Medicines and Devices) product equivalent for pharmaceutical product identification.

• emis_code_id (PK): The unique identifier for the drug code
• dmd_product_code_id (FK): Links to the DMD product code

Examples

Get all drug codes for an organisation

SELECT *
FROM hive.explorer_recruit_anon.drug_code
WHERE organisation = 'CDB-0001'
LIMIT 100;

Find drug codes with a DMD product code

SELECT
  emis_code_id,
  emis_term,
  dmd_product_code_id
FROM hive.explorer_recruit_anon.drug_code
WHERE dmd_product_code_id IS NOT NULL
LIMIT 100;

Look up a specific drug by EMIS code

SELECT
  emis_code_id,
  emis_term,
  emis_old_code,
  dmd_product_code_id
FROM hive.explorer_recruit_anon.drug_code
WHERE emis_code_id = 'D123'
LIMIT 100;