Re: [Gnumed-bugs] a query problem when bootstrapping bundle [v18-v19-dynamic]

[Karsten Hilbert]

On Fri, Nov 22, 2013 at 12:09:12AM +0000, Jim Busser wrote:

> > Based on that I'll release 1.4.1 / 19.1 this week.
> 
> The revision of the above was insufficient to remedy my data situation, but I 
> seem to have figured out that below
> 
>       v19-clin-hospital_stay-dynamic.sql
> 
>       lines 32 … 46
> 
> ==========================================
> insert into dem.org_unit (fk_org, description)
> select
>       (select pk from dem.org where dem.org.description = c_hs.narrative 
> order by pk limit 1),
>       'unit of ' || c_hs.narrative
> from
>       clin.hospital_stay c_hs
> where
>       not exists (
>               select 1 from dem.org_unit
>               where
>                       dem.org_unit.description = 'unit of ' || c_hs.narrative
>                               and
>                       fk_org = (select pk from dem.org where 
> dem.org.description = c_hs.narrative order by pk limit 1)
>       )
> ==========================================
> 
> in spite of LIMIT 1, still returns duplicate records:
> 
>        pk   |                 ?column?                 
>       -------+------------------------------------------
>        24712 | unit of Enterprise Healthcare Unit
>        24709 | unit of VGH
>        24710 | unit of Vancouver General - Med Day Care
>        24710 | unit of Vancouver General - Med Day Care
>        24713 | unit of Vancouver General Hospital
>        24714 | unit of Vanc
>       (6 rows)
> 
> whereas if I instead add a 'distinct' to line 33
> 
> 32    insert into dem.org_unit (fk_org, description)
> 33    select distinct ...
> 
> this succeeded to insert 5 rows without hitting the duplicate constraint 
> shown in the attached log.

While the DISTINCT is not the best solution it
clearly identified the problem. I have slightly
improved upon it (I hope). Please retry with
the attached files put in place.

> PS we are not done yet with fixing server 19.1 package ... I next hit a 
> problem when the bootstrapper could not import
> 
>       cannot open file 
> [../sql/v18-v19/fixups/v19-dem-lnk_org_unit2ext_id-fixup.sql]
>       
>       --> see next email

Looking forward ...

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

v19-clin-hospital_stay-dynamic.sql
Description: application/sql

v19-clin-procedure-dynamic.sql
Description: application/sql