Re: [Gnumed-devel] Getting Canadian drug data into FreeDiams

[Karsten Hilbert]

On Fri, Feb 19, 2010 at 03:12:08AM -0800, Jim Busser wrote:

> Do you want to know something remarkable? I actually think
> I am close to done (including scripts) for the current
> structure of FreeDiams,

Nice !


> I only just still have this problem populating the CIS table, as mapped at
> 
>       http://wiki.gnumed.de/bin/view/Gnumed/FreeDiams

Regarding the mapping:

in the CIS_CIP table the CIS field needs to be populated by
the Canadian drug.txt.DRUG_CODE value just like the CIS
table CIS field. Same with the COMPO.CIS field. Just for
clarification. It'll be the same DRUG_CODE (that's
intentional) and that should be pointed out explicitely.

> because it wants only a single record defining the drug's
> availability, whereas Health Canada data provides a
> historical record of notices. Accordingly there exist
> multiple values of
> 
>       CURRENT_STATUS_FLAG
> 
> with varying ages of records, per drug. Presumably I need
> to make the SELECT command ignore values *except* for the
> newest per drug else some way to first delete the non-newest
> records per drug.

SELECT *
FROM ...
WHERE
        ...

                and

        CURRENT_STATUS_FLAG = the_value_of_records_of_interest
;


> Below is where I got stuck:

How ?

> INSERT INTO CIS (CIS, DENOMINATION, FORME, ADMINISTRATION, COMMERCIALISATION)
> SELECT A1.DRUG_CODE, A1.BRAND_NAME, A2.PHARMACEUTICAL_FORM, 
> A3.ROUTE_OF_ADMINISTRATION, A4.CURRENT_STATUS_FLAG
> FROM drug A1, form A2, route A3, status A4
> WHERE A1.DRUG_CODE = A2.DRUG_CODE …

See my other post.

> ... also would the above fail if extended across >2 tables?

Not sure I understand what you mean by "extend".

> ... does such a select need primary or other keys (indexing) ?

No.

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346