gnumed-devel
[Top][All Lists]
Advanced

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

[Gnumed-devel] Transaction for creating rows in link tables


From: Jim Busser
Subject: [Gnumed-devel] Transaction for creating rows in link tables
Date: Tue, 13 Sep 2011 14:18:32 -0700

Pending resolution of whether is_live can be NULL, the following nearly 
succeeds to add vaccines where they do not already exist in clin.vaccine

    INSERT INTO clin.vaccine  (
      is_live,
      fk_brand,
      id_route
    )
    SELECT
        NULL, r_bd.pk, c_vr.id
        FROM ref.branded_drug AS r_bd
        INNER JOIN staging.vaccine s_v
           ON s_v.fk_external_code = r_bd.external_code
        INNER JOIN clin.vacc_route c_vr
          ON UPPER(c_vr.description) = UPPER(s_v.route)
        WHERE r_bd.fk_data_source = (SELECT pk from Ref.data_source WHERE 
position('FreeDiams' in name_short) > 0)
        AND NOT EXISTS
                        (SELECT 1
                        FROM clin.vaccine c_v
                        INNER JOIN ref.branded_drug as r_bd
                          ON c_v.fk_brand = r_bd.pk
                        WHERE r_bd.fk_data_source = (SELECT pk from 
Ref.data_source WHERE position('FreeDiams' in name_short) > 0)
                        AND s_v.fk_external_code =      r_bd.external_code
                        LIMIT 1
                        )
    ;

except for the constraint

        ERROR: [clin.vaccine]:
        INSERT/UPDATE failed: no indication linked to vaccine 
(clin.lnk_vaccine2inds.fk_vaccine <-(82)-> clin.vaccine.pk) 

Therefore if adding vaccines depends on (at the same time) creating links to 
indications, would I wrap the above as foo1 inside

        begin;
        foo1 ;
        foo2 ;
        end; (or commit;)

followed by foo2 where foo2 inserts suitable records into

        clin.lnk_vaccine2inds.fk_vaccine

??

-- Jim




reply via email to

[Prev in Thread] Current Thread [Next in Thread]