Skip to content
TheChatArchiveThe Chat Archive
gpt-4o-mini-2024-07-18· OpenAIAi2 WildChat-4.8M (AI2)

Translate this sql query in portuguese : WITH sums as ( SELECT lfat."admin1" as…

  1. User

    Translate this sql query in portuguese : WITH sums as ( SELECT lfat."admin1" as "Province", lfat."admin2" as "District", lfat."admin4" as "Health Facility",

       string_to_array(lfat."signs_and_symptoms_of_an_acute_attack", ' ') as array_sign_sympt_acut_attack,
       
    

    array( SELECT CASE WHEN q = 'Redness_of_limb' THEN ' Redness of limb ' WHEN q = 'Warmth_of_limb' THEN ' Warmth of limb ' WHEN q = 'Increased_swelling_of_limb' THEN ' Increased swelling of limb ' WHEN q = 'Painful_limb' THEN ' Painful limb ' WHEN q = 'Fever' THEN ' Fever ' WHEN q = 'Headache' THEN ' Headache ' WHEN q = 'Chills' THEN ' Chills ' WHEN q = 'Nausea_vomiting' THEN ' Nausea_vomiting ' WHEN q = 'other' THEN ' Other ' WHEN q = 'Dont_know_any_signs_and_symptoms_of_an_acute_attack' THEN ' Don''t know any signs/symptoms of acute attac ' ELSE q END FROM UNNEST(string_to_array(lfat."signs_and_symptoms_of_an_acute_attack", ' ')::varchar[]) q) AS "Signs and symptoms of acute attack"

    FROM lfat WHERE 1=1 [[and {{county}}]] [[and {{district}}]] [[and {{facilty}}]] [[and {{date_ass}}]]

    ORDER BY lfat."admin1" ASC, lfat."admin2" ASC, lfat."admin4" ASC )

    SELECT "Province", "District", "Health Facility", "Signs and symptoms of acute attack", case when 'Dont_know_any_signs_and_symptoms_of_an_acute_attack' = ANY(array_sign_sympt_acut_attack) then cast(coalesce(array_length(array_sign_sympt_acut_attack, 1), 0) as numeric)*(1) - 1 when cast(coalesce(array_length(array_sign_sympt_acut_attack, 1), 0) as numeric)*1>=1 then 1 else 0 end as "Score - Signs and symptoms of acute attack" FROM sums

  2. Assistant

    Aqui está a tradução da consulta SQL para o português:

    COM somas AS (
    SELECT lfat."admin1" AS "Província",
           lfat."admin2" AS "Distrito",
           lfat."admin4" AS "Unidade de Saúde",
           
           string_to_array(lfat."sinais_e_sintomas_de_um_attack_agudo", ' ') AS array_sinais_sintomas_attack_agudo,
           
    array(
        SELECT CASE WHEN q = 'Vermelhidão_do_membro' THEN ' Vermelhidão do membro ' 
                    WHEN q = 'Calor_do_membro' THEN ' Calor do membro ' 
                    WHEN q = 'Aumento_do_inchaço_do_membro' THEN ' Aumento do inchaço do membro ' 
                    WHEN q = 'Membro_dolorido' THEN ' Membro dolorido '
                    WHEN q = 'Febre' THEN ' Febre '
                    WHEN q = 'Dor_de_cabeça' THEN ' Dor de cabeça '
                    WHEN q = 'Calafrios' THEN ' Calafrios '
                    WHEN q = 'Náusea_e_vômito' THEN ' Náusea e vômito '
                    WHEN q = 'outro' THEN ' Outro ' 
                    WHEN q = 'Não_sei_quais_sinais_e_sintomas_de_um_attack_agudo' THEN ' Não sei quais sinais/sintomas de um attack agudo ' 
               ELSE q END 
        FROM UNNEST(string_to_array(lfat."sinais_e_sintomas_de_um_attack_agudo", ' ')::varchar[]) q) AS "Sinais e sintomas de ataque agudo"
        
    FROM lfat
    WHERE 1=1 
          [[AND {{condado}}]]
          [[AND {{distrito}}]]
          [[AND {{unidade}}]]
          [[AND {{data_ass}}]]
          
    ORDER BY lfat."admin1" ASC, lfat."admin2" ASC, lfat."admin4" ASC
    )
    
    SELECT "Província",
           "Distrito",
           "Unidade de Saúde",
           "Sinais e sintomas de ataque agudo",
           CASE WHEN 'Não_sei_quais_sinais_e_sintomas_de_um_attack_agudo' = ANY(array_sinais_sintomas_attack_agudo) THEN CAST(COALESCE(array_length(array_sinais_sintomas_attack_agudo, 1), 0) AS numeric)*(1) - 1
                WHEN CAST(COALESCE(array_length(array_sinais_sintomas_attack_agudo, 1), 0) AS numeric)*1>=1 THEN 1
                ELSE 0 END AS "Pontuação - Sinais e sintomas de ataque agudo"
    FROM somas
    

    Essa tradução mantém a estrutura e a lógica da consulta original, mas altera os nomes das colunas e as condições para o português.

  3. User

    rerun the previous query. Do ot translate the reference. Only column name

  4. Assistant

    Sure! Here’s the SQL query with only the column names translated to Portuguese while keeping the references unchanged:

    WITH sums AS (
    SELECT lfat."admin1" AS "Província",
           lfat."admin2" AS "Distrito",
           lfat."admin4" AS "Unidade de Saúde",
           
           string_to_array(lfat."signs_and_symptoms_of_an_acute_attack", ' ') AS array_sign_sympt_acut_attack,
           
    array(
        SELECT CASE WHEN q = 'Redness_of_limb' THEN ' Vermelhidão do membro ' 
                    WHEN q = 'Warmth_of_limb' THEN ' Calor do membro ' 
                    WHEN q = 'Increased_swelling_of_limb' THEN ' Aumento do inchaço do membro ' 
                    WHEN q = 'Painful_limb' THEN ' Membro dolorido '
                    WHEN q = 'Fever' THEN ' Febre '
                    WHEN q = 'Headache' THEN ' Dor de cabeça '
                    WHEN q = 'Chills' THEN ' Calafrios '
                    WHEN q = 'Nausea_vomiting' THEN ' Náusea e vômito '
                    WHEN q = 'other' THEN ' Outro ' 
                    WHEN q = 'Dont_know_any_signs_and_symptoms_of_an_acute_attack' THEN ' Não sei quais sinais/sintomas de um ataque agudo ' 
               ELSE q END 
        FROM UNNEST(string_to_array(lfat."signs_and_symptoms_of_an_acute_attack", ' ')::varchar[]) q) AS "Sinais e sintomas de ataque agudo"
        
    FROM lfat
    WHERE 1=1 
          [[AND {{county}}]]
          [[AND {{district}}]]
          [[AND {{facilty}}]]
          [[AND {{date_ass}}]]
          
    ORDER BY lfat."admin1" ASC, lfat."admin2" ASC, lfat."admin4" ASC
    )
    
    SELECT "Província",
           "Distrito",
           "Unidade de Saúde",
           "Sinais e sintomas de ataque agudo",
           CASE WHEN 'Dont_know_any_signs_and_symptoms_of_an_acute_attack' = ANY(array_sign_sympt_acut_attack) THEN CAST(COALESCE(array_length(array_sign_sympt_acut_attack, 1), 0) AS numeric)*(1) - 1
                WHEN CAST(COALESCE(array_length(array_sign_sympt_acut_attack, 1), 0) AS numeric)*1>=1 THEN 1
                ELSE 0 END AS "Pontuação - Sinais e sintomas de ataque agudo"
    FROM sums
    

    In this version, only the column names have been translated to Portuguese, while the references remain unchanged.

  5. User

    rerun again using CTE (with)

  6. Assistant

    Certainly! Here’s the SQL query using Common Table Expressions (CTE) while keeping the original references and translating only the column names into Portuguese:

    WITH somas AS (
    SELECT lfat."admin1" AS "Província",
           lfat."admin2" AS "Distrito",
           lfat."admin4" AS "Unidade de Saúde",
           
           string_to_array(lfat."signs_and_symptoms_of_an_acute_attack", ' ') AS array_sign_sympt_acut_attack,
           
           array(
               SELECT CASE WHEN q = 'Redness_of_limb' THEN ' Vermelhidão do membro ' 
                           WHEN q = 'Warmth_of_limb' THEN ' Calor do membro ' 
                           WHEN q = 'Increased_swelling_of_limb' THEN ' Aumento do inchaço do membro ' 
                           WHEN q = 'Painful_limb' THEN ' Membro dolorido '
                           WHEN q = 'Fever' THEN ' Febre '
                           WHEN q = 'Headache' THEN ' Dor de cabeça '
                           WHEN q = 'Chills' THEN ' Calafrios '
                           WHEN q = 'Nausea_vomiting' THEN ' Náusea e vômito '
                           WHEN q = 'other' THEN ' Outro ' 
                           WHEN q = 'Dont_know_any_signs_and_symptoms_of_an_acute_attack' THEN ' Não sei quais sinais/sintomas de um ataque agudo ' 
                      ELSE q END 
               FROM UNNEST(string_to_array(lfat."signs_and_symptoms_of_an_acute_attack", ' ')::varchar[]) q
           ) AS "Sinais e sintomas de ataque agudo"
           
    FROM lfat
    WHERE 1=1 
          [[AND {{county}}]]
          [[AND {{district}}]]
          [[AND {{facilty}}]]
          [[AND {{date_ass}}]]
          
    ORDER BY lfat."admin1" ASC, lfat."admin2" ASC, lfat."admin4" ASC
    )
    
    SELECT "Província",
           "Distrito",
           "Unidade de Saúde",
           "Sinais e sintomas de ataque agudo",
           CASE WHEN 'Dont_know_any_signs_and_symptoms_of_an_acute_attack' = ANY(array_sign_sympt_acut_attack) THEN CAST(COALESCE(array_length(array_sign_sympt_acut_attack, 1), 0) AS numeric)*(1) - 1
                WHEN CAST(COALESCE(array_length(array_sign_sympt_acut_attack, 1), 0) AS numeric)*1 >= 1 THEN 1
                ELSE 0 
           END AS "Pontuação - Sinais e sintomas de ataque agudo"
    FROM somas
    

    This version maintains the use of a Common Table Expression (CTE) with the name somas, and only the column names have been translated into Portuguese, while the references remain unchanged.

  7. User

    same as above : WITH sums as ( SELECT lfat."admin1" as "Province", lfat."admin2" as "District", lfat."admin4" as "Health Facility",

       string_to_array(lfat."strategies_to_treat_for_acute_attacks", ' ') as strategies_to_treat,
    

    array( SELECT CASE WHEN q = 'Cool_leg_in_bucket' THEN ' Cool leg in bucket of cool water or using a cold compress ' WHEN q = 'Analgesic_or_anti-inflammatory_medications' THEN ' Analgesic or anti-inflammatory medications ' WHEN q = 'Topical_antibiotics' THEN ' Topical antibiotics ' WHEN q = 'Oral_antibiotics' THEN ' Oral antibiotics ' WHEN q = 'Injectable_antibiotics' THEN ' Injectable antibiotics ' WHEN q = 'Rest' THEN ' Rest ' WHEN q = 'Elevation' THEN ' Elevation ' WHEN q = 'Provide_fluids' THEN ' Provide fluids ' WHEN q = 'Advise_patient_to_avoid_exercises' THEN ' Advise patient to avoid exercises for duration of acute attack ' WHEN q = 'Other' THEN ' Other ' WHEN q = 'Dont_know_any_acute_attack_treatments' THEN ' Don’t know any acute attack treatments ' ELSE q END FROM UNNEST(string_to_array(lfat."strategies_to_treat_for_acute_attacks", ' ')::varchar[]) q) AS "Strategies you would use to treat a patient with acute attack"

    FROM lfat WHERE 1=1 [[and {{county}}]] [[and {{district}}]] [[and {{facilty}}]] [[and {{date_ass}}]]

    ORDER BY lfat."admin1" ASC, lfat."admin2" ASC, lfat."admin4" ASC )

    SELECT "Province", "District", "Health Facility", "Strategies you would use to treat a patient with acute attack", case when 'Dont_know_any_acute_attack_treatments' = ANY(strategies_to_treat) then cast(coalesce(array_length(strategies_to_treat, 1), 0) as numeric)*(1) - 1 when cast(coalesce(array_length(strategies_to_treat, 1), 0) as numeric)*1>=1 then 1 else 0 end as "Score - Strategies you would use to treat a patient with acute attack" FROM sums

  8. Assistant

    Here’s the SQL query using Common Table Expressions (CTE) with only the column names translated into Portuguese while keeping the original references unchanged:

    WITH somas AS (
    SELECT lfat."admin1" AS "Província",
           lfat."admin2" AS "Distrito",
           lfat."admin4" AS "Unidade de Saúde",
           
           string_to_array(lfat."strategies_to_treat_for_acute_attacks", ' ') AS strategies_to_treat,
           array(
               SELECT CASE WHEN q = 'Cool_leg_in_bucket' THEN ' Perna fresca em balde com água fria ou usando um compressa fria ' 
                           WHEN q = 'Analgesic_or_anti-inflammatory_medications' THEN ' Medicamentos analgésicos ou anti-inflamatórios ' 
                           WHEN q = 'Topical_antibiotics' THEN ' Antibióticos tópicos ' 
                           WHEN q = 'Oral_antibiotics' THEN ' Antibióticos orais '
                           WHEN q = 'Injectable_antibiotics' THEN ' Antibióticos injetáveis '
                           WHEN q = 'Rest' THEN ' Repouso '
                           WHEN q = 'Elevation' THEN ' Elevação '
                           WHEN q = 'Provide_fluids' THEN ' Fornecer líquidos '
                           WHEN q = 'Advise_patient_to_avoid_exercises' THEN ' Aconselhar o paciente a evitar exercícios durante o ataque agudo '
                           WHEN q = 'Other' THEN ' Outro ' 
                           WHEN q = 'Dont_know_any_acute_attack_treatments' THEN ' Não sei quais são os tratamentos para ataque agudo ' 
                      ELSE q END 
               FROM UNNEST(string_to_array(lfat."strategies_to_treat_for_acute_attacks", ' ')::varchar[]) q) AS "Estratégias que você usaria para tratar um paciente com ataque agudo"
           
    FROM lfat
    WHERE 1=1 
          [[AND {{county}}]]
          [[AND {{district}}]]
          [[AND {{facilty}}]]
          [[AND {{date_ass}}]]
          
    ORDER BY lfat."admin1" ASC, lfat."admin2" ASC, lfat."admin4" ASC
    )
    
    SELECT "Província",
           "Distrito",
           "Unidade de Saúde",
           "Estratégias que você usaria para tratar um paciente com ataque agudo",
           CASE WHEN 'Dont_know_any_acute_attack_treatments' = ANY(strategies_to_treat) THEN CAST(COALESCE(array_length(strategies_to_treat, 1), 0) AS numeric)*(1) - 1
                WHEN CAST(COALESCE(array_length(strategies_to_treat, 1), 0) AS numeric)*1 >= 1 THEN 1 
                ELSE 0 
           END AS "Pontuação - Estratégias que você usaria para tratar um paciente com ataque agudo"
    FROM somas
    

    In this version, the original references have been preserved, while the column names have been translated into Portuguese. The CTE is named somas.

  9. User

    same as above : SPLIT_PART(nullif(lfat."GPS",''), ' ', 1)::decimal as "Latitude",

       SPLIT_PART(nullif(lfat."GPS",''), ' ', 2)::decimal as "Longitude",
       
       (case when lfat."facilitytype"='health_c' then 'Health Centre'
             when lfat."facilitytype"='general_h' then 'General Hospital'
             when lfat."facilitytype"='district_h' then 'District hospital'
             when lfat."facilitytype"='subdist_comm_h' then 'Sub-district/community hospital'
             when lfat."facilitytype"='prov_reg_h' then 'Regional referral'
             when lfat."facilitytype"='chc' then 'Community Health Centre'
             when lfat."facilitytype"='national_h' then 'National'
             else lfat."facilitytype_specify"
       end) as "Type of Facility",
       (case when lfat."authority"='gov_public' then 'Government/Public'
             when lfat."authority"='ngo' then 'NGO/Not-for-profit'
             when lfat."authority"='private' then 'Private-for-profit'
             when lfat."authority"='mission' then 'Mission/Faith-based'
             else lfat."authority_specify"
       end) as "Managing authority",
       lfat."interviewer" as "Assessor",
       lfat."date" as "Date",
       
       SUM((case when lfat."staff_trained_last_2_years"='yes' then '1'
             else '0'
       end)::integer) as "Score - Staff re/trained in lymph. mngmt in the last 2 years",
    
       SUM((case when lfat."mmdp_materials_present"='yes' then '1'
             else '0'
       end)::integer) as "Score - Existing lymphoedema management guidelines",
    
       SUM((case when lfat."materials_written_in_local_language"='none_seen' then '0'
             else '1'
        end)::integer) as "Score - Existing education/awareness materials written in the local language",
    
       SUM((case when lfat."watersource"='piped_supply_inside_the_building' then '1'
                 when lfat."watersource"='piped_supply_outside_the_building' then '1'
                 when lfat."watersource"='tube_well' then '1'
                 when lfat."watersource"='protected_well' then '1'
                 when lfat."watersource"='protected_spring' then '1'
                 when lfat."watersource"='rainwater' then '1'
                 when lfat."watersource"='tanker_truck' then '1'
             else '0'
       end)::integer) as "Score - Main source of water",
    
       SUM((case when lfat."watersource_location"='on_premises' then '1'
             else '0'
       end)::integer) as "Score - Main water supply location",
    
       SUM((case when lfat."confirm_water_is_available"='water_available' then '1'
             else '0'
       end)::integer) as "Score - Water source available",
    
       SUM((case when lfat."Antiseptic"='in_stock_sufficient' then '1'
             else '0'
       end)::integer) as "Score - Antiseptic",
    
        SUM((case when lfat."antifungal"='in_stock_sufficient' then '1'
             else '0'
       end)::integer) as "Score - Antifungal",
    
       SUM(cast((case when lfat."analgesic_or_anti-inflammatory"='in_stock_sufficient'  then '1'
             else '0'
       end) as integer)) as "Score - Analgesic or anti-inflammatory",
    
       SUM((case when lfat."injectable_antibiotics"='in_stock_sufficient' or lfat."oral_antibiotics"='in_stock_sufficient' then '1'
             else '0'
       end)::integer) as "Score - Oral/Injectable antibiotics",
    
       SUM((case when lfat."bucket_or_basin"='in_stock_sufficient' then '1'
             else '0'
       end)::integer) as "Score - Bucket or basin",
    
       SUM((case when lfat."soap"='in_stock_sufficient' then '1'
             else '0'
       end)::integer) as "Score - Soap",
    
       SUM(cast((case when lfat."towels"='in_stock_sufficient' then '1'
             else '0'
       end) as integer)) as "Score - Towels",
    
       SUM((case when lfat."Gauze_or_cotton_cloth"='in_stock_sufficient' then '1'
             else '0'
       end)::integer) as "Score - Gauze or cotton cloth",
    
       SUM((case when lfat."cold_compress"='in_stock_sufficient' then '1'
             else '0'
       end)::integer) as "Score - Cold compress",
    
       SUM((case when lfat."nail_clippers"='in_stock_sufficient' then '1'
             else '0'
       end)::integer) as "Score - Nail clippers",
    
       SUM((case when lfat."patient_hygiene_kits"='in_stock_sufficient' then '1'
             else '0'
       end)::integer) as "Score - Patient hygiene kits",
    
       SUM((case when lfat."existing_system_for_identifying_and_quatifying"='yes' then '1'
             else '0'
       end)::integer) as "Score - Patient Tracking System",
    
      case when lfat."patients_with_lymphoedema"::integer>=1 then 1
         else 0
      end as "Score - Patients with lymphedema (#)",
    
      string_to_array(lfat."signs_and_symptoms_of_lymphedema", ' ') as array_sign_sympt_lymph,
       
    

    array( SELECT CASE WHEN q = 'Swelling_reversible_at_night' THEN ' Swelling (reversible at night) ' WHEN q = 'Swelling_irreversible' THEN ' Swelling (irreversible) ' WHEN q = 'Skin_folds' THEN ' Skin folds (shallow or deep) ' WHEN q = 'Knobs_on_the_skin' THEN ' Knobs on the skin ' WHEN q = 'Mossy_lesions' THEN ' Mossy lesions (i.e. small elongated or rounded growths) ' WHEN q = 'Inability_to_perform_daily_activitie' THEN ' Inability to perform daily activities or care for self ' WHEN q = 'Acute_attacks_adenolymphangitis' THEN ' Acute attacks / adenolymphangitis ' WHEN q = 'Wounds_or_entry_lesions' THEN ' Wounds or entry lesions ' WHEN q = 'other' THEN ' Other ' WHEN q = 'Dont_know_any_signs_symptoms' THEN ' Don''t know any signs/symptoms of lymphedema ' ELSE q END FROM UNNEST(string_to_array(lfat."signs_and_symptoms_of_lymphedema", ' ')::varchar[]) q) AS "Signs and symptoms of lymphedema",

    string_to_array(lfat."signs_and_symptoms_of_an_acute_attack", ' ') as array_sign_sympt_acut_attack,
       
    

    array( SELECT CASE WHEN q = 'Redness_of_limb' THEN ' Redness of limb ' WHEN q = 'Warmth_of_limb' THEN ' Warmth of limb ' WHEN q = 'Increased_swelling_of_limb' THEN ' Increased swelling of limb ' WHEN q = 'Painful_limb' THEN ' Painful limb ' WHEN q = 'Fever' THEN ' Fever ' WHEN q = 'Headache' THEN ' Headache ' WHEN q = 'Chills' THEN ' Chills ' WHEN q = 'Nausea_vomiting' THEN ' Nausea_vomiting ' WHEN q = 'other' THEN ' Other ' WHEN q = 'Dont_know_any_signs_and_symptoms_of_an_acute_attack' THEN ' Don''t know any signs/symptoms of acute attac ' ELSE q END FROM UNNEST(string_to_array(lfat."signs_and_symptoms_of_an_acute_attack", ' ')::varchar[]) q) AS "Signs and symptoms of acute attack",

    string_to_array(lfat."strategies_to_teach_for_lymph_acute_attacks", ' ') as strategies_to_teach,
    

    array( SELECT CASE WHEN q = 'Hygiene_Washing_and_drying_the_affected_limb' THEN ' Hygiene/Washing and drying the affected limb ' WHEN q = 'Wound_care_entrance_lesion_care' THEN ' Wound care/entrance lesion care ' WHEN q = 'Elevation' THEN ' Elevation ' WHEN q = 'Rest' THEN ' Rest ' WHEN q = 'Exercises' THEN ' Exercises ' WHEN q = 'Shoe_use' THEN ' Shoe use ' WHEN q = 'Prophylactic_creams' THEN ' Prophylactic creams ' WHEN q = 'Prophylactic_systemic_antibiotics' THEN ' Prophylactic systemic antibiotics ' WHEN q = 'traditional_remedies' THEN ' Atraditional remedies ' WHEN q = 'Other' THEN ' Other ' WHEN q = 'Dont_know_any_lymphedema_management_techniques' THEN ' Don’t know any lymphedema management techniques ' ELSE q END FROM UNNEST(string_to_array(lfat."strategies_to_teach_for_lymph_acute_attacks", ' ')::varchar[]) q) AS "Strategies you would teach a patient with lymphoedema",

      string_to_array(lfat."strategies_to_treat_for_acute_attacks", ' ') as strategies_to_treat,
    

    array( SELECT CASE WHEN q = 'Cool_leg_in_bucket' THEN ' Cool leg in bucket of cool water or using a cold compress ' WHEN q = 'Analgesic_or_anti-inflammatory_medications' THEN ' Analgesic or anti-inflammatory medications ' WHEN q = 'Topical_antibiotics' THEN ' Topical antibiotics ' WHEN q = 'Oral_antibiotics' THEN ' Oral antibiotics ' WHEN q = 'Injectable_antibiotics' THEN ' Injectable antibiotics ' WHEN q = 'Rest' THEN ' Rest ' WHEN q = 'Elevation' THEN ' Elevation ' WHEN q = 'Provide_fluids' THEN ' Provide fluids ' WHEN q = 'Advise_patient_to_avoid_exercises' THEN ' Advise patient to avoid exercises for duration of acute attack ' WHEN q = 'Other' THEN ' Other ' WHEN q = 'Dont_know_any_acute_attack_treatments' THEN ' Don’t know any acute attack treatments ' ELSE q END FROM UNNEST(string_to_array(lfat."strategies_to_treat_for_acute_attacks", ' ')::varchar[]) q) AS "Strategies you would use to treat a patient with acute attack"

  10. Assistant

    Here's the SQL query with only the column names translated into Portuguese while keeping the original references intact, using CTE (Common Table Expression):

    WITH somas AS (
    SELECT SPLIT_PART(NULLIF(lfat."GPS", ''), ' ', 1)::decimal AS "Latitude",
           SPLIT_PART(NULLIF(lfat."GPS", ''), ' ', 2)::decimal AS "Longitude",
           
           (CASE WHEN lfat."facilitytype" = 'health_c' THEN 'Centro de Saúde'
                 WHEN lfat."facilitytype" = 'general_h' THEN 'Hospital Geral'
                 WHEN lfat."facilitytype" = 'district_h' THEN 'Hospital Distrital'
                 WHEN lfat."facilitytype" = 'subdist_comm_h' THEN 'Hospital Sub-distrital/Comunitário'
                 WHEN lfat."facilitytype" = 'prov_reg_h' THEN 'Encaminhamento Regional'
                 WHEN lfat."facilitytype" = 'chc' THEN 'Centro de Saúde Comunitário'
                 WHEN lfat."facilitytype" = 'national_h' THEN 'Nacional'
                 ELSE lfat."facilitytype_specify"
           END) AS "Tipo de Unidade",
           
           (CASE WHEN lfat."authority" = 'gov_public' THEN 'Governo/Público'
                 WHEN lfat."authority" = 'ngo' THEN 'ONG/Sem fins lucrativos'
                 WHEN lfat."authority" = 'private' THEN 'Privado com fins lucrativos'
                 WHEN lfat."authority" = 'mission' THEN 'Missão/Religioso'
                 ELSE lfat."authority_specify"
           END) AS "Autoridade Gestora",
           
           lfat."interviewer" AS "Assessor",
           lfat."date" AS "Data",
           
           SUM((CASE WHEN lfat."staff_trained_last_2_years" = 'yes' THEN '1'
                     ELSE '0'
           END)::integer) AS "Pontuação - Funcionários treinados/re-treinados em manejo de linfedema nos últimos 2 anos",
    
           SUM((CASE WHEN lfat."mmdp_materials_present" = 'yes' THEN '1'
                     ELSE '0'
           END)::integer) AS "Pontuação - Diretrizes de manejo de linfedema existentes",
    
           SUM((CASE WHEN lfat."materials_written_in_local_language" = 'none_seen' THEN '0'
                     ELSE '1'
            END)::integer) AS "Pontuação - Materiais de educação/conscientização existentes escritos no idioma local",
    
           SUM((CASE WHEN lfat."watersource" = 'piped_supply_inside_the_building' THEN '1'
                     WHEN lfat."watersource" = 'piped_supply_outside_the_building' THEN '1'
                     WHEN lfat."watersource" = 'tube_well' THEN '1'
                     WHEN lfat."watersource" = 'protected_well' THEN '1'
                     WHEN lfat."watersource" = 'protected_spring' THEN '1'
                     WHEN lfat."watersource" = 'rainwater' THEN '1'
                     WHEN lfat."watersource" = 'tanker_truck' THEN '1'
                 ELSE '0'
           END)::integer) AS "Pontuação - Principal fonte de água",
    
           SUM((CASE WHEN lfat."watersource_location" = 'on_premises' THEN '1'
                     ELSE '0'
           END)::integer) AS "Pontuação - Local da principal fonte de água",
    
           SUM((CASE WHEN lfat."confirm_water_is_available" = 'water_available' THEN '1'
                     ELSE '0'
           END)::integer) AS "Pontuação - Disponibilidade da fonte de água",
    
           SUM((CASE WHEN lfat."Antiseptic" = 'in_stock_sufficient' THEN '1'
                     ELSE '0'
           END)::integer) AS "Pontuação - Antisséptico",
    
           SUM((CASE WHEN lfat."antifungal" = 'in_stock_sufficient' THEN '1'
                     ELSE '0'
           END)::integer) AS "Pontuação - Antifúngico",
    
           SUM(CAST((CASE WHEN lfat."analgesic_or_anti-inflammatory" = 'in_stock_sufficient' THEN '1'
                     ELSE '0'
           END) AS integer)) AS "Pontuação - Analgésico ou anti-inflamatório",
    
           SUM((CASE WHEN lfat."injectable_antibiotics" = 'in_stock_sufficient' OR lfat."oral_antibiotics" = 'in_stock_sufficient' THEN '1'
                     ELSE '0'
           END)::integer) AS "Pontuação - Antibióticos orais/injetáveis",
    
           SUM((CASE WHEN lfat."bucket_or_basin" = 'in_stock_sufficient' THEN '1'
                     ELSE '0'
           END)::integer) AS "Pontuação - Balde ou bacia",
    
           SUM((CASE WHEN lfat."soap" = 'in_stock_sufficient' THEN '1'
                     ELSE '0'
           END)::integer) AS "Pontuação - Sabão",
    
           SUM(CAST((CASE WHEN lfat."towels" = 'in_stock_sufficient' THEN '1'
                     ELSE '0'
           END) AS integer)) AS "Pontuação - Toalhas",
    
           SUM((CASE WHEN lfat."Gauze_or_cotton_cloth" = 'in_stock_sufficient' THEN '1'
                     ELSE '0'
           END)::integer) AS "Pontuação - Gazes ou panos de algodão",
    
           SUM((CASE WHEN lfat."cold_compress" = 'in_stock_sufficient' THEN '1'
                     ELSE '0'
           END)::integer) AS "Pontuação - Compressa fria",
    
           SUM((CASE WHEN lfat."nail_clippers" = 'in_stock_sufficient' THEN '1'
                     ELSE '0'
           END)::integer) AS "Pontuação - Cortadores de unha",
    
           SUM((CASE WHEN lfat."patient_hygiene_kits" = 'in_stock_sufficient' THEN '1'
                     ELSE '0'
           END)::integer) AS "Pontuação - Kits de higiene do paciente",
    
           SUM((CASE WHEN lfat."existing_system_for_identifying_and_quatifying" = 'yes' THEN '1'
                     ELSE '0'
           END)::integer) AS "Pontuação - Sistema de Rastreio de Pacientes",
    
           CASE WHEN lfat."patients_with_lymphoedema"::integer >= 1 THEN 1
                ELSE 0
           END AS "Pontuação - Pacientes com linfedema (#)",
    
           string_to_array(lfat."signs_and_symptoms_of_lymphedema", ' ') AS array_sign_sympt_lymph,
           
           array(
               SELECT CASE WHEN q = 'Swelling_reversible_at_night' THEN ' Inchaço (reversível à noite) ' 
                           WHEN q = 'Swelling_irreversible' THEN ' Inchaço (irreversível) ' 
                           WHEN q = 'Skin_folds' THEN ' Pregas de pele (rasas ou profundas) ' 
                           WHEN q = 'Knobs_on_the_skin' THEN ' Nódulos na pele '
                           WHEN q = 'Mossy_lesions' THEN ' Lesões musgosas (ou seja, crescimentos pequenos e alongados ou arredondados) '
                           WHEN q = 'Inability_to_perform_daily_activitie' THEN ' Incapacidade de realizar atividades diárias ou cuidar de si mesmo '
                           WHEN q = 'Acute_attacks_adenolymphangitis' THEN ' Ataques agudos / adenolinfangite '
                           WHEN q = 'Wounds_or_entry_lesions' THEN ' Feridas ou lesões de entrada '
                           WHEN q = 'other' THEN ' Outro ' 
                           WHEN q = 'Dont_know_any_signs_symptoms' THEN ' Não sei quais sinais/sintomas de linfedema ' 
                      ELSE q END 
               FROM UNNEST(string_to_array(lfat."signs_and_symptoms_of_lymphedema", ' ')::varchar[]) q
           ) AS "Sinais e sintomas de linfedema",
        
           string_to_array(lfat."signs_and_symptoms_of_an_acute_attack", ' ') AS array_sign_sympt_acut_attack,
           
           array(
               SELECT CASE WHEN q = 'Redness_of_limb' THEN ' Vermelhidão do membro ' 
                           WHEN q = 'Warmth_of_limb' THEN ' Calor do membro ' 
                           WHEN q = 'Increased_swelling_of_limb' THEN ' Aumento do inchaço do membro ' 
                           WHEN q = 'Painful_limb' THEN ' Membro dolorido '
                           WHEN q = 'Fever' THEN ' Febre '
                           WHEN q = 'Headache' THEN ' Dor de cabeça '
                           WHEN q = 'Chills' THEN ' Calafrios '
                           WHEN q = 'Nausea_vomiting' THEN ' Náusea e vômito '
                           WHEN q = 'other' THEN ' Outro ' 
                           WHEN q = 'Dont_know_any_signs_and_symptoms_of_an_acute_attack' THEN ' Não sei quais sinais/sintomas de ataque agudo ' 
                      ELSE q END 
               FROM UNNEST(string_to_array(lfat."signs_and_symptoms_of_an_acute_attack", ' ')::varchar[]) q
           ) AS "Sinais e sintomas de ataque agudo",
        
           string_to_array(lfat."strategies_to_teach_for_lymph_acute_attacks", ' ') AS strategies_to_teach,
           array(
               SELECT CASE WHEN q = 'Hygiene_Washing_and_drying_the_affected_limb' THEN ' Higiene/Lavar e secar o membro afetado ' 
                           WHEN q = 'Wound_care_entrance_lesion_care' THEN ' Cuidados com feridas/cuidados com lesões de entrada ' 
                           WHEN q = 'Elevation' THEN ' Elevação ' 
                           WHEN q = 'Rest' THEN ' Repouso '
                           WHEN q = 'Exercises' THEN ' Exercícios '
                           WHEN q = 'Shoe_use' THEN ' Uso de sapato '
                           WHEN q = 'Prophylactic_creams' THEN ' Cremes profiláticos '
                           WHEN q = 'Prophylactic_systemic_antibiotics' THEN ' Antibióticos sistêmicos profiláticos '
                           WHEN q = 'traditional_remedies' THEN ' Remédios tradicionais '
                           WHEN q = 'Other' THEN ' Outro ' 
                           WHEN q = 'Dont_know_any_lymphedema_management_techniques' THEN ' Não sei quais técnicas de manejo de linfedema ' 
                      ELSE q END 
               FROM UNNEST(string_to_array(lfat."strategies_to_teach_for_lymph_acute_attacks", ' ')::varchar[]) q
           ) AS "Estratégias que você ensinaria a um paciente com linfedema",
    
           string_to_array(lfat."strategies_to_treat_for_acute_attacks", ' ') AS strategies_to_treat,
           array(
               SELECT CASE WHEN q = 'Cool_leg_in_bucket' THEN ' Perna fresca em balde com água fria ou usando uma compressa fria ' 
                           WHEN q = 'Analgesic_or_anti-inflammatory_medications' THEN ' Medicamentos analgésicos ou anti-inflamatórios ' 
                           WHEN q = 'Topical_antibiotics' THEN ' Antibióticos tópicos ' 
                           WHEN q = 'Oral_antibiotics' THEN ' Antibióticos orais '
                           WHEN q = 'Injectable_antibiotics' THEN ' Antibióticos injetáveis '
                           WHEN q = 'Rest' THEN ' Repouso '
                           WHEN q = 'Elevation' THEN ' Elevação '
                           WHEN q = 'Provide_fluids' THEN ' Fornecer líquidos '
                           WHEN q = 'Advise_patient_to_avoid_exercises' THEN ' Aconselhar o paciente a evitar exercícios durante o ataque agudo '
                           WHEN q = 'Other' THEN ' Outro ' 
                           WHEN q = 'Dont_know_any_acute_attack_treatments' THEN ' Não sei quais são os tratamentos para ataque agudo ' 
                      ELSE q END 
               FROM UNNEST(string_to_array(lfat."strategies_to_treat_for_acute_attacks", ' ')::varchar[]) q
           ) AS "Estratégias que você usaria para tratar um paciente com ataque agudo"
    )
    

    In this version, the original references have been preserved, while the column names have been translated into Portuguese. The CTE is named somas.

  11. User

    translate this sql query extract in portuguese by keeping the reference. ORDER BY lfat."admin1" ASC, lfat."admin2" ASC, lfat."admin4" ASC )

    SELECT "Province", "District", "Health Facility", "Latitude", "Longitude", "Type of Facility", "Managing authority", "Assessor",

       "Score - Staff re/trained in lymph. mngmt in the last 2 years"
     as "2. Trained staff",
    
       "Score - Existing lymphoedema management guidelines"
      +"Score - Existing education/awareness materials written in the local language"
     as "3. Case management and Educational Materials",
    
       case when "Score - Main source of water" + "Score - Main water supply location" + "Score - Water source available"=3 then 1
            else 0 end
       as "4. Infrastructure",
    
       "Score - Antiseptic"
      +"Score - Antifungal"
      +"Score - Analgesic or anti-inflammatory"
      +"Score - Oral/Injectable antibiotics"
      +(case when "Score - Bucket or basin" + "Score - Soap" + "Score - Towels" + "Score - Gauze or cotton cloth" + 
                 "Score - Cold compress" + "Score - Nail clippers" + "Score - Patient hygiene kits" >=2 then 2
            when "Score - Bucket or basin" + "Score - Soap" + "Score - Towels" + "Score - Gauze or cotton cloth" + 
                 "Score - Cold compress" + "Score - Nail clippers" + "Score - Patient hygiene kits" =1 then 1
            when "Score - Bucket or basin" + "Score - Soap" + "Score - Towels" + "Score - Gauze or cotton cloth" + 
                 "Score - Cold compress" + "Score - Nail clippers" + "Score - Patient hygiene kits" =0 then 0
       end) as "5. Medications and commodities",
    
       case when "Score - Patient Tracking System" + "Score - Patients with lymphedema (#)"=2 then 1
            else 0 end
     as "6. Patient Tracking System",
    
      (case when 'Dont_know_any_signs_symptoms'= ANY(array_sign_sympt_lymph) then cast(coalesce(array_length(array_sign_sympt_lymph, 1), 0) as numeric)*(1) - 1
            when cast(coalesce(array_length(array_sign_sympt_lymph, 1), 0) as numeric)*1>=1 then 1 
            else 0 end)
     +(case when 'Dont_know_any_signs_and_symptoms_of_an_acute_attack' = ANY(array_sign_sympt_acut_attack) then cast(coalesce(array_length(array_sign_sympt_acut_attack, 1), 0) as numeric)*(1) - 1
            when cast(coalesce(array_length(array_sign_sympt_acut_attack, 1), 0) as numeric)*1>=1 then 1 
            else 0 end)
     +(case when 'Dont_know_any_lymphedema_management_techniques' = ANY(strategies_to_teach) then cast(coalesce(array_length(strategies_to_teach, 1), 0) as numeric)*(1) - 1
            when cast(coalesce(array_length(strategies_to_teach, 1), 0) as numeric)*1>=1 then 1 
            else 0 end)
     +(case when 'Dont_know_any_acute_attack_treatments' = ANY(strategies_to_treat) then cast(coalesce(array_length(strategies_to_treat, 1), 0) as numeric)*(1) - 1
            when cast(coalesce(array_length(strategies_to_treat, 1), 0) as numeric)*1>=1 then 1 
            else 0 end)
     as "7. Staff knowledge",
     
     /***********************TOTAL***********************/
     
       "Score - Staff re/trained in lymph. mngmt in the last 2 years"
      +"Score - Existing lymphoedema management guidelines"
      +"Score - Existing education/awareness materials written in the local language"
      +(case when "Score - Main source of water" + "Score - Main water supply location" + "Score - Water source available"=3 then 1
            else 0 end)
      +"Score - Antiseptic"
      +"Score - Antifungal"
      +"Score - Analgesic or anti-inflammatory"
      +"Score - Oral/Injectable antibiotics"
      +(case when "Score - Bucket or basin" + "Score - Soap" + "Score - Towels" + "Score - Gauze or cotton cloth" + 
                 "Score - Cold compress" + "Score - Nail clippers" + "Score - Patient hygiene kits" >=2 then 2
            when "Score - Bucket or basin" + "Score - Soap" + "Score - Towels" + "Score - Gauze or cotton cloth" + 
                 "Score - Cold compress" + "Score - Nail clippers" + "Score - Patient hygiene kits" =1 then 1
            when "Score - Bucket or basin" + "Score - Soap" + "Score - Towels" + "Score - Gauze or cotton cloth" + 
                 "Score - Cold compress" + "Score - Nail clippers" + "Score - Patient hygiene kits" =0 then 0
       end)
      +(case when "Score - Patient Tracking System" + "Score - Patients with lymphedema (#)"=2 then 1
            else 0 end)
      +(case when 'Dont_know_any_signs_symptoms'= ANY(array_sign_sympt_lymph) then cast(coalesce(array_length(array_sign_sympt_lymph, 1), 0) as numeric)*(1) - 1
            when cast(coalesce(array_length(array_sign_sympt_lymph, 1), 0) as numeric)*1>=1 then 1 
            else 0 end)
     +(case when 'Dont_know_any_signs_and_symptoms_of_an_acute_attack' = ANY(array_sign_sympt_acut_attack) then cast(coalesce(array_length(array_sign_sympt_acut_attack, 1), 0) as numeric)*(1) - 1
            when cast(coalesce(array_length(array_sign_sympt_acut_attack, 1), 0) as numeric)*1>=1 then 1 
            else 0 end)
     +(case when 'Dont_know_any_lymphedema_management_techniques' = ANY(strategies_to_teach) then cast(coalesce(array_length(strategies_to_teach, 1), 0) as numeric)*(1) - 1
            when cast(coalesce(array_length(strategies_to_teach, 1), 0) as numeric)*1>=1 then 1 
            else 0 end)
     +(case when 'Dont_know_any_acute_attack_treatments' = ANY(strategies_to_treat) then cast(coalesce(array_length(strategies_to_treat, 1), 0) as numeric)*(1) - 1
            when cast(coalesce(array_length(strategies_to_treat, 1), 0) as numeric)*1>=1 then 1 
            else 0 end) || ' / 14 '
      || '( '||
       round(((
       "Score - Staff re/trained in lymph. mngmt in the last 2 years"
      +"Score - Existing lymphoedema management guidelines"
      +"Score - Existing education/awareness materials written in the local language"
      +(case when "Score - Main source of water" + "Score - Main water supply location" + "Score - Water source available"=3 then 1
            else 0 end)
      +"Score - Antiseptic"
      +"Score - Antifungal"
      +"Score - Analgesic or anti-inflammatory"
      +"Score - Oral/Injectable antibiotics"
      +(case when "Score - Bucket or basin" + "Score - Soap" + "Score - Towels" + "Score - Gauze or cotton cloth" + 
                 "Score - Cold compress" + "Score - Nail clippers" + "Score - Patient hygiene kits" >=2 then 2
            when "Score - Bucket or basin" + "Score - Soap" + "Score - Towels" + "Score - Gauze or cotton cloth" + 
                 "Score - Cold compress" + "Score - Nail clippers" + "Score - Patient hygiene kits" =1 then 1
            when "Score - Bucket or basin" + "Score - Soap" + "Score - Towels" + "Score - Gauze or cotton cloth" + 
                 "Score - Cold compress" + "Score - Nail clippers" + "Score - Patient hygiene kits" =0 then 0
       end)
      +(case when "Score - Patient Tracking System" + "Score - Patients with lymphedema (#)"=2 then 1
            else 0 end)
      +(case when 'Dont_know_any_signs_symptoms'= ANY(array_sign_sympt_lymph) then cast(coalesce(array_length(array_sign_sympt_lymph, 1), 0) as numeric)*(1) - 1
            when cast(coalesce(array_length(array_sign_sympt_lymph, 1), 0) as numeric)*1>=1 then 1 
            else 0 end)
     +(case when 'Dont_know_any_signs_and_symptoms_of_an_acute_attack' = ANY(array_sign_sympt_acut_attack) then cast(coalesce(array_length(array_sign_sympt_acut_attack, 1), 0) as numeric)*(1) - 1
            when cast(coalesce(array_length(array_sign_sympt_acut_attack, 1), 0) as numeric)*1>=1 then 1 
            else 0 end)
     +(case when 'Dont_know_any_lymphedema_management_techniques' = ANY(strategies_to_teach) then cast(coalesce(array_length(strategies_to_teach, 1), 0) as numeric)*(1) - 1
            when cast(coalesce(array_length(strategies_to_teach, 1), 0) as numeric)*1>=1 then 1 
            else 0 end)
     +(case when 'Dont_know_any_acute_attack_treatments' = ANY(strategies_to_treat) then cast(coalesce(array_length(strategies_to_treat, 1), 0) as numeric)*(1) - 1
            when cast(coalesce(array_length(strategies_to_treat, 1), 0) as numeric)*1>=1 then 1 
            else 0 end) )/0.14)::decimal,2) || '% )'
     as "Total Score - LFAT",
     
     /*Score LFAT Hidden*/
      "Score - Staff re/trained in lymph. mngmt in the last 2 years"
      +"Score - Existing lymphoedema management guidelines"
      +"Score - Existing education/awareness materials written in the local language"
      +(case when "Score - Main source of water" + "Score - Main water supply location" + "Score - Water source available"=3 then 1
            else 0 end)
      +"Score - Antiseptic"
      +"Score - Antifungal"
      +"Score - Analgesic or anti-inflammatory"
      +"Score - Oral/Injectable antibiotics"
      +(case when "Score - Bucket or basin" + "Score - Soap" + "Score - Towels" + "Score - Gauze or cotton cloth" + 
                 "Score - Cold compress" + "Score - Nail clippers" + "Score - Patient hygiene kits" >=2 then 2
            when "Score - Bucket or basin" + "Score - Soap" + "Score - Towels" + "Score - Gauze or cotton cloth" + 
                 "Score - Cold compress" + "Score - Nail clippers" + "Score - Patient hygiene kits" =1 then 1
            when "Score - Bucket or basin" + "Score - Soap" + "Score - Towels" + "Score - Gauze or cotton cloth" + 
                 "Score - Cold compress" + "Score - Nail clippers" + "Score - Patient hygiene kits" =0 then 0
       end)
      +(case when "Score - Patient Tracking System" + "Score - Patients with lymphedema (#)"=2 then 1
            else 0 end)
      +(case when 'Dont_know_any_signs_symptoms'= ANY(array_sign_sympt_lymph) then cast(coalesce(array_length(array_sign_sympt_lymph, 1), 0) as numeric)*(1) - 1
            when cast(coalesce(array_length(array_sign_sympt_lymph, 1), 0) as numeric)*1>=1 then 1 
            else 0 end)
     +(case when 'Dont_know_any_signs_and_symptoms_of_an_acute_attack' = ANY(array_sign_sympt_acut_attack) then cast(coalesce(array_length(array_sign_sympt_acut_attack, 1), 0) as numeric)*(1) - 1
            when cast(coalesce(array_length(array_sign_sympt_acut_attack, 1), 0) as numeric)*1>=1 then 1 
            else 0 end)
     +(case when 'Dont_know_any_lymphedema_management_techniques' = ANY(strategies_to_teach) then cast(coalesce(array_length(strategies_to_teach, 1), 0) as numeric)*(1) - 1
            when cast(coalesce(array_length(strategies_to_teach, 1), 0) as numeric)*1>=1 then 1 
            else 0 end)
     +(case when 'Dont_know_any_acute_attack_treatments' = ANY(strategies_to_treat) then cast(coalesce(array_length(strategies_to_treat, 1), 0) as numeric)*(1) - 1
            when cast(coalesce(array_length(strategies_to_treat, 1), 0) as numeric)*1>=1 then 1 
            else 0 end)
    as "Total Score - Hidden"
     
    

    FROM sums

  12. Assistant

    Here’s the SQL query excerpt translated into Portuguese, keeping the references unchanged:

    ORDER BY lfat."admin1" ASC, lfat."admin2" ASC, lfat."admin4" ASC
    )
    
    SELECT "Província",
           "Distrito",
           "Unidade de Saúde",
           "Latitude",
           "Longitude",
           "Tipo de Unidade",
           "Autoridade Gestora",
           "Assessor",
           
           "Pontuação - Funcionários treinados/re-treinados em manejo de linfedema nos últimos 2 anos"
           AS "2. Funcionários treinados",
    
           "Pontuação - Diretrizes de manejo de linfedema existentes"
          + "Pontuação - Materiais de educação/conscientização existentes escritos no idioma local"
           AS "3. Gestão de Casos e Materiais Educacionais",
    
           CASE WHEN "Pontuação - Principal fonte de água" + "Pontuação - Local da principal fonte de água" + "Pontuação - Disponibilidade da fonte de água" = 3 THEN 1
                ELSE 0 END
           AS "4. Infraestrutura",
    
           "Pontuação - Antisséptico"
          + "Pontuação - Antifúngico"
          + "Pontuação - Analgésico ou anti-inflamatório"
          + "Pontuação - Antibióticos orais/injetáveis"
          + (CASE WHEN "Pontuação - Balde ou bacia" + "Pontuação - Sabão" + "Pontuação - Toalhas" + "Pontuação - Gazes ou panos de algodão" + 
                     "Pontuação - Compressa fria" + "Pontuação - Cortadores de unha" + "Pontuação - Kits de higiene do paciente" >= 2 THEN 2
                WHEN "Pontuação - Balde ou bacia" + "Pontuação - Sabão" + "Pontuação - Toalhas" + "Pontuação - Gazes ou panos de algodão" + 
                     "Pontuação - Compressa fria" + "Pontuação - Cortadores de unha" + "Pontuação - Kits de higiene do paciente" = 1 THEN 1
                WHEN "Pontuação - Balde ou bacia" + "Pontuação - Sabão" + "Pontuação - Toalhas" + "Pontuação - Gazes ou panos de algodão" + 
                     "Pontuação - Compressa fria" + "Pontuação - Cortadores de unha" + "Pontuação - Kits de higiene do paciente" = 0 THEN 0
           END) AS "5. Medicamentos e suprimentos",
    
           CASE WHEN "Pontuação - Sistema de Rastreio de Pacientes" + "Pontuação - Pacientes com linfedema (#)" = 2 THEN 1
                ELSE 0 END
           AS "6. Sistema de Rastreio de Pacientes",
    
          (CASE WHEN 'Não_sei_quais_sinais_sintomas' = ANY(array_sign_sympt_lymph) THEN CAST(COALESCE(array_length(array_sign_sympt_lymph, 1), 0) AS numeric) * (1) - 1
                WHEN CAST(COALESCE(array_length(array_sign_sympt_lymph, 1), 0) AS numeric) * 1 >= 1 THEN 1 
                ELSE 0 END)
         + (CASE WHEN 'Não_sei_quais_sinais_e_sintomas_de_um_attack_agudo' = ANY(array_sign_sympt_acut_attack) THEN CAST(COALESCE(array_length(array_sign_sympt_acut_attack, 1), 0) AS numeric) * (1) - 1
                WHEN CAST(COALESCE(array_length(array_sign_sympt_acut_attack, 1), 0) AS numeric) * 1 >= 1 THEN 1 
                ELSE 0 END)
         + (CASE WHEN 'Não_sei_quais_técnicas_de_manejo_de_linfedema' = ANY(strategies_to_teach) THEN CAST(COALESCE(array_length(strategies_to_teach, 1), 0) AS numeric) * (1) - 1
                WHEN CAST(COALESCE(array_length(strategies_to_teach, 1), 0) AS numeric) * 1 >= 1 THEN 1 
                ELSE 0 END)
         + (CASE WHEN 'Não_sei_quais_tratamentos_de_attack_agudo' = ANY(strategies_to_treat) THEN CAST(COALESCE(array_length(strategies_to_treat, 1), 0) AS numeric) * (1) - 1
                WHEN CAST(COALESCE(array_length(strategies_to_treat, 1), 0) AS numeric) * 1 >= 1 THEN 1 
                ELSE 0 END)
         AS "7. Conhecimento da equipe",
         
         /***********************TOTAL***********************/
         
           "Pontuação - Funcionários treinados/re-treinados em manejo de linfedema nos últimos 2 anos"
          + "Pontuação - Diretrizes de manejo de linfedema existentes"
          + "Pontuação - Materiais de educação/conscientização existentes escritos no idioma local"
          + (CASE WHEN "Pontuação - Principal fonte de água" + "Pontuação - Local da principal fonte de água" + "Pontuação - Disponibilidade da fonte de água" = 3 THEN 1
                ELSE 0 END)
          + "Pontuação - Antisséptico"
          + "Pontuação - Antifúngico"
          + "Pontuação - Analgésico ou anti-inflamatório"
          + "Pontuação - Antibióticos orais/injetáveis"
          + (CASE WHEN "Pontuação - Balde ou bacia" + "Pontuação - Sabão" + "Pontuação - Toalhas" + "Pontuação - Gazes ou panos de algodão" + 
                     "Pontuação - Compressa fria" + "Pontuação - Cortadores de unha" + "Pontuação - Kits de higiene do paciente" >= 2 THEN 2
                WHEN "Pontuação - Balde ou bacia" + "Pontuação - Sabão" + "Pontuação - Toalhas" + "Pontuação - Gazes ou panos de algodão" + 
                     "Pontuação - Compressa fria" + "Pontuação - Cortadores de unha" + "Pontuação - Kits de higiene do paciente" = 1 THEN 1
                WHEN "Pontuação - Balde ou bacia" + "Pontuação - Sabão" + "Pontuação - Toalhas" + "Pontuação - Gazes ou panos de algodão" + 
                     "Pontuação - Compressa fria" + "Pontuação - Cortadores de unha" + "Pontuação - Kits de higiene do paciente" = 0 THEN 0
           END)
          + (CASE WHEN "Pontuação - Sistema de Rastreio de Pacientes" + "Pontuação - Pacientes com linfedema (#)" = 2 THEN 1
                ELSE 0 END)
          + (CASE WHEN 'Não_sei_quais_sinais_sintomas' = ANY(array_sign_sympt_lymph) THEN CAST(COALESCE(array_length(array_sign_sympt_lymph, 1), 0) AS numeric) * (1) - 1
                WHEN CAST(COALESCE(array_length(array_sign_sympt_lymph, 1), 0) AS numeric) * 1 >= 1 THEN 1 
                ELSE 0 END)
         + (CASE WHEN 'Não_sei_quais_sinais_e_sintomas_de_um_attack_agudo' = ANY(array_sign_sympt_acut_attack) THEN CAST(COALESCE(array_length(array_sign_sympt_acut_attack, 1), 0) AS numeric) * (1) - 1
                WHEN CAST(COALESCE(array_length(array_sign_sympt_acut_attack, 1), 0) AS numeric) * 1 >= 1 THEN 1 
                ELSE 0 END)
         + (CASE WHEN 'Não_sei_quais_técnicas_de_manejo_de_linfedema' = ANY(strategies_to_teach) THEN CAST(COALESCE(array_length(strategies_to_teach, 1), 0) AS numeric) * (1) - 1
                WHEN CAST(COALESCE(array_length(strategies_to_teach, 1), 0) AS numeric) * 1 >= 1 THEN 1 
                ELSE 0 END)
         + (CASE WHEN 'Não_sei_quais_tratamentos_de_attack_agudo' = ANY(strategies_to_treat) THEN CAST(COALESCE(array_length(strategies_to_treat, 1), 0) AS numeric) * (1) - 1
                WHEN CAST(COALESCE(array_length(strategies_to_treat, 1), 0) AS numeric) * 1 >= 1 THEN 1 
                ELSE 0 END) || ' / 14 '
          || '( ' ||
           ROUND(((
           "Pontuação - Funcionários treinados/re-treinados em manejo de linfedema nos últimos 2 anos"
          + "Pontuação - Diretrizes de manejo de linfedema existentes"
          + "Pontuação - Materiais de educação/conscientização existentes escritos no idioma local"
          + (CASE WHEN "Pontuação - Principal fonte de água" + "Pontuação - Local da principal fonte de água" + "Pontuação - Disponibilidade da fonte de água" = 3 THEN 1
                ELSE 0 END)
          + "Pontuação - Antisséptico"
          + "Pontuação - Antifúngico"
          + "Pontuação - Analgésico ou anti-inflamatório"
          + "Pontuação - Antibióticos orais/injetáveis"
          + (CASE WHEN "Pontuação - Balde ou bacia" + "Pontuação - Sabão" + "Pontuação - Toalhas" + "Pontuação - Gazes ou panos de algodão" + 
                     "Pontuação - Compressa fria" + "Pontuação - Cortadores de unha" + "Pontuação - Kits de higiene do paciente" >= 2 THEN 2
                WHEN "Pontuação - Balde ou bacia" + "Pontuação - Sabão" + "Pontuação - Toalhas" + "Pontuação - Gazes ou panos de algodão" + 
                     "Pontuação - Compressa fria" + "Pontuação - Cortadores de unha" + "Pontuação - Kits de higiene do paciente" = 1 THEN 1
                WHEN "Pontuação - Balde ou bacia" + "Pontuação - Sabão" + "Pontuação - Toalhas" + "Pontuação - Gazes ou panos de algodão" + 
                     "Pontuação - Compressa fria" + "Pontuação - Cortadores de unha" + "Pontuação - Kits de higiene do paciente" = 0 THEN 0
           END)
          + (CASE WHEN "Pontuação - Sistema de Rastreio de Pacientes" + "Pontuação - Pacientes com linfedema (#)" = 2 THEN 1
                ELSE 0 END)
          + (CASE WHEN 'Não_sei_quais_sinais_sintomas' = ANY(array_sign_sympt_lymph) THEN CAST(COALESCE(array_length(array_sign_sympt_lymph, 1), 0) AS numeric) * (1) - 1
                WHEN CAST(COALESCE(array_length(array_sign_sympt_lymph, 1), 0) AS numeric) * 1 >= 1 THEN 1 
                ELSE 0 END)
         + (CASE WHEN 'Não_sei_quais_sinais_e_sintomas_de_um_attack_agudo' = ANY(array_sign_sympt_acut_attack) THEN CAST(COALESCE(array_length(array_sign_sympt_acut_attack, 1), 0) AS numeric) * (1) - 1
                WHEN CAST(COALESCE(array_length(array_sign_sympt_acut_attack, 1), 0) AS numeric) * 1 >= 1 THEN 1 
                ELSE 0 END)
         + (CASE WHEN 'Não_sei_quais_técnicas_de_manejo_de_linfedema' = ANY(strategies_to_teach) THEN CAST(COALESCE(array_length(strategies_to_teach, 1), 0) AS numeric) * (1) - 1
                WHEN CAST(COALESCE(array_length(strategies_to_teach, 1), 0) AS numeric) * 1 >= 1 THEN 1 
                ELSE 0 END)
         + (CASE WHEN 'Não_sei_quais_tratamentos_de_attack_agudo' = ANY(strategies_to_treat) THEN CAST(COALESCE(array_length(strategies_to_treat, 1), 0) AS numeric) * (1) - 1
                WHEN CAST(COALESCE(array_length(strategies_to_treat, 1), 0) AS numeric) * 1 >= 1 THEN 1 
                ELSE 0 END) ) / 0.14)::decimal, 2) || '% )'
         AS "Pontuação Total - LFAT",
         
         /* Pontuação LFAT Oculta */
          "Pontuação - Funcionários treinados/re-treinados em manejo de linfedema nos últimos 2 anos"
          + "Pontuação - Diretrizes de manejo de linfedema existentes"
          + "Pontuação - Materiais de educação/conscientização existentes escritos no idioma local"
          + (CASE WHEN "Pontuação - Principal fonte de água" + "Pontuação - Local da principal fonte de água" + "Pontuação - Disponibilidade da fonte de água" = 3 THEN 1
                ELSE 0 END)
          + "Pontuação - Antisséptico"
          + "Pontuação - Antifúngico"
          + "Pontuação - Analgésico ou anti-inflamatório"
          + "Pontuação - Antibióticos orais/injetáveis"
          + (CASE WHEN "Pontuação - Balde ou bacia" + "Pontuação - Sabão" + "Pontuação - Toalhas" + "Pontuação - Gazes ou panos de algodão" + 
                     "Pontuação - Compressa fria" + "Pontuação - Cortadores de unha" + "Pontuação - Kits de higiene do paciente" >= 2 THEN 2
                WHEN "Pontuação - Balde ou bacia" + "Pontuação - Sabão" + "Pontuação - Toalhas" + "Pontuação - Gazes ou panos de algodão" + 
                     "Pontuação - Compressa fria" + "Pontuação - Cortadores de unha" + "Pontuação - Kits de higiene do paciente" = 1 THEN 1
                WHEN "Pontuação - Balde ou bacia" + "Pontuação - Sabão" + "Pontuação - Toalhas" + "Pontuação - Gazes ou panos de algodão" + 
                     "Pontuação - Compressa fria" + "Pontuação - Cortadores de unha" + "Pontuação - Kits de higiene do paciente" = 0 THEN 0
           END)
          + (CASE WHEN "Pontuação - Sistema de Rastreio de Pacientes" + "Pontuação - Pacientes com linfedema (#)" = 2 THEN 1
                ELSE 0 END)
          + (CASE WHEN 'Não_sei_quais_sinais_sintomas' = ANY(array_sign_sympt_lymph) THEN CAST(COALESCE(array_length(array_sign_sympt_lymph, 1), 0) AS numeric) * (1) - 1
                WHEN CAST(COALESCE(array_length(array_sign_sympt_lymph, 1), 0) AS numeric) * 1 >= 1 THEN 1 
                ELSE 0 END)
         + (CASE WHEN 'Não_sei_quais_sinais_e_sintomas_de_um_attack_agudo' = ANY(array_sign_sympt_acut_attack) THEN CAST(COALESCE(array_length(array_sign_sympt_acut_attack, 1), 0) AS numeric) * (1) - 1
                WHEN CAST(COALESCE(array_length(array_sign_sympt_acut_attack, 1), 0) AS numeric) * 1 >= 1 THEN 1 
                ELSE 0 END)
         + (CASE WHEN 'Não_sei_quais_técnicas_de_manejo_de_linfedema' = ANY(strategies_to_teach) THEN CAST(COALESCE(array_length(strategies_to_teach, 1), 0) AS numeric) * (1) - 1
                WHEN CAST(COALESCE(array_length(strategies_to_teach, 1), 0) AS numeric) * 1 >= 1 THEN 1 
                ELSE 0 END)
         + (CASE WHEN 'Não_sei_quais_tratamentos_de_attack_agudo' = ANY(strategies_to_treat) THEN CAST(COALESCE(array_length(strategies_to_treat, 1), 0) AS numeric) * (1) - 1
                WHEN CAST(COALESCE(array_length(strategies_to_treat, 1), 0) AS numeric) * 1 >= 1 THEN 1 
                ELSE 0 END)
        AS "Pontuação Total - Oculta"
         
    FROM somas
    

    In this translation, the column names and other descriptive texts have been translated to Portuguese while keeping the actual references as they are.