gnumed-devel
[Top][All Lists]
Advanced

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

[Gnumed-devel] Key pairs (natural, calculated) and WHERE NOT EXISTS


From: Jim Busser
Subject: [Gnumed-devel] Key pairs (natural, calculated) and WHERE NOT EXISTS
Date: Fri, 16 Sep 2011 01:44:45 -0700

I am a bit stuck developing an approach to the following...

I am looking to assemble a transaction which:

- in the first part, inserts vaccines from an external source *where* no 
vaccine yet existed for fk_brand

- and which, in the second part, inserts key value pairs (fk_vaccine, 
fk_indication) where this pair did not already exist in the link table

The challenge is in how to 'pass' and express the second member of the pair 
inferiorly into a

        WHERE NOT EXISTS

clause, because the candidate values for fk_indication would be calculated from 
a series of CASE statements

        CASE WHEN position('cholera' in lower(substance_description)) > 0 THEN
                        (SELECT id FROM clin.vacc_indication WHERE 
position('cholera' in lower(clin.vacc_indication.description)) > 0)

however I can't seem to generate an alias from the CASE statements. For example 
I cannot do the following to generate an alias 'val' to then pass 'val' into 
WHERE NOT EXISTS:

        CASE WHEN … THEN
                (SELECT value) AS val

        WHERE NOT EXISTS
                        (SELECT 1
                        FROM clin.vaccine
                        INNER JOIN clin.lnk_vaccine2inds
                        ON clin.lnk_vaccine2inds.fk_vaccine = clin.vaccine.pk
                                AND c_lv.fk_indication = val
                        LIMIT 1
                        )


So… is there a way… or no way… to pass the set of candidate calculated values 
into the WHERE NOT EXISTS table?

Is my only option to write the candidate values into a staging table, and then 
assemble the candidate link pairs via join?


-- Jim




reply via email to

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