[Top][All Lists]
[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
- [Gnumed-devel] Key pairs (natural, calculated) and WHERE NOT EXISTS,
Jim Busser <=