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

[Karsten Hilbert]

On Fri, Sep 16, 2011 at 01:44:45AM -0700, Jim Busser wrote:

> 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…

There is - you would need to repeat the entire CASE sequence
in the NOT EXISTS condition - but it may get quite
cumbersome.

It may help to generate a whole new SQL statement for each
indication such that each INSERT already knows and does not
need a CASE.

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

That would be another option.

A third option might be to create a suitable plpgsql
function and run that appropriately.

Karsten
-- 
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346