Re: [Gnumed-devel] Updating dem listings for Brazil

[Busser, Jim]

On 2011-11-15, at 2:58 AM, Karsten Hilbert wrote:

>>      AM   | Amapa  --> should be AP
>>      AZ   | Amazonas  --> should be AM
>>      MG   | Mato Grosso  --> should be MT
>>      MR   | Minas Gerais  --> should be MG
>>      PR   | Paraiba  --> should be PB
>>      PN   | Parana  --> should be PR
>> 
>> Can / should the above corrections be scripted into the next db fixup?
> 
> Them being (data) bugs I am ready to accept an SQL file.
> Nonetheless they should be included with a potential
> Brazilian data pack (as corrections).

The Brazilian data pack will include

    UPDATE dem.state SET code = 'AP' WHERE code = 'AM' AND name = 'Amapa' AND 
country = 'BR';
    UPDATE dem.state SET code = 'AM' WHERE code = 'AZ' AND name = 'Amazonas'  
AND country = 'BR';
    UPDATE dem.state SET code = 'MT' WHERE code = 'MG' AND name = 'Mato Grosso' 
 AND country = 'BR';
    UPDATE dem.state SET code = 'PB' WHERE code = 'PR' AND name = 'Paraiba'  
AND country = 'BR';
    UPDATE dem.state SET code = 'PR' WHERE code = 'PN' AND name = 'Parana'  AND 
country = 'BR';
    
But I am wondering how to provide the i18n support comparable to

        insert into dem.state (code, country, name) values ('AB', 'CA', 
i18n.i18n('Alberta')); 
        
when the values for as-yet-missing state names will come from a staging table 
in the SELECT DISTINCT line below ...

    INSERT INTO dem.state (
      code,
      name,
      country
    )
    SELECT DISTINCT ON (UPPER(s_state.abbr)) UPPER(s_state.abbr), s_state.name, 
'BR'
    FROM staging.state s_state
    -- INNER JOIN dem.state d_state
    -- ON UPPER(d_state.code) = CASE WHEN s_state.abbr IS NULL THEN '??' ELSE 
UPPER(s_state.abbr) END
    -- INNER JOIN dem.country d_c
    -- ON UPPER(d_state.country)=UPPER(d_c.code)
    WHERE s_state.abbr IS NOT NULL AND s_state.name IS NOT NULL
    AND NOT EXISTS
      (SELECT 1 FROM dem.state d_state WHERE UPPER(d_state.code) = CASE WHEN 
s_state.abbr IS NULL THEN '??' ELSE UPPER(s_state.abbr) END
      AND d_state.country = (SELECT code FROM dem.country d_c WHERE 
UPPER('Brazil') =UPPER(d_c.name))
      )
    ORDER BY UPPER(s_state.abbr)
    ;

therefore, to provide support for the i18n, will that function work if applied 
to the source column 's_state.name' in the following fashion:

    SELECT DISTINCT ON (UPPER(s_state.abbr)) UPPER(s_state.abbr),  
i18n.i18n(s_state.name), 'BR'
    FROM staging.state s_state ...

??

-- Jim