BNF codes for all products with "inhalation" route

[sebbacon]

SELECT
  bnf_code,
  name,
  "desc"
FROM
  dmd_product
LEFT JOIN
  dmd_ont
ON
  dmd_product.vpid = dmd_ont.vpid
INNER JOIN
  dmd_lookup_ont_form_route
ON
  dmd_lookup_ont_form_route.cd = dmd_ont.formcd
WHERE
  "desc" LIKE '%.inhalation'
  AND bnf_code IS NOT NULL;```

[sebbacon]

codes.zip

[sebbacon]

Same thing with CFC flag:

SELECT
  bnf_code,
  name,
  "desc",
  cfc_f
FROM
  dmd_product
LEFT JOIN
  dmd_ont
ON
  dmd_product.vpid = dmd_ont.vpid
INNER JOIN
  dmd_lookup_ont_form_route
ON
  dmd_lookup_ont_form_route.cd = dmd_ont.formcd
JOIN
  dmd_vmp
ON
  dmd_vmp.vpid = dmd_product.vpid
WHERE
  "desc" LIKE '%.inhalation'
  AND bnf_code IS NOT NULL

codes.zip

[richiecroker]

There's a problem with the coding, we've found a couple of items that didn't have any ontology information:

Dose form information may be an alternative if it is consistent:

[sebbacon]

Dose for for all things that do have form/route set as %.inhalation:

┌───────────────────────────────────────────┐
│                   form                    │
├───────────────────────────────────────────┤
│ Liquid                                    │
│ Impregnated cigarette                     │
│ Inhalation powder                         │
│ Inhalation gas                            │
│ Powder for nebuliser solution             │
│ Pressurised inhalation                    │
│ Powder for solution for injection         │
│ Ointment                                  │
│ Inhalation vapour                         │
│ Powder and solvent for nebuliser solution │
│ Nebuliser liquid                          │
└───────────────────────────────────────────┘

That includes Vaporub and stuff.

The counts for form versus form-route on just pressurized inhalation are the same, however:

127.0.0.1 prescribing@prescribing=# select count(*) from tmp_adqs where form_route = 'pressurizedinhalation.inhalation';
┌───────┐
│ count │
├───────┤
│   213 │
└───────┘
(1 row)

Time: 509.354 ms
127.0.0.1 prescribing@prescribing=# select count(*) from tmp_adqs where form = 'Pressurised inhalation';
┌───────┐
│ count │
├───────┤
│   213 │
└───────┘
(1 row)

Time: 624.888 ms

[richiecroker]

weird - see example above for FLutform - possibly a different 3 missing items?