problem with query

From: Roberto Scattini <roberto(dot)scattini(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: problem with query
Date: 2013-09-12 20:34:57
Message-ID: CAApYZY8LOgsm8DSYCTJECYa4n4236DvrER6rfAgYX17mPZHGRQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

hi, today we discovered that this query doesn't return the expected values:

SELECT DISTINCT
p.id, p.apellido AS "Apellido", p.nombre AS "Nombre", pf.nombre AS
"Funcion", to_char(da.f_ingreso_pg, 'dd/mm/yyyy') AS "Fecha Ingreso PG",
e.estado AS "Estado", to_char(pe.fecha, 'dd/mm/yyyy') AS "Fecha Estado"
FROM personal.personas AS p
LEFT JOIN personal.personas_da_pg AS da ON p.id=da.id_persona
LEFT JOIN personal.personas_estado AS pe ON da.estado_id=pe.id
LEFT JOIN personal.estados AS e ON pe.id_estado=e.id
LEFT JOIN procu_departamento AS d ON d.id=da.id_departamento
LEFT JOIN procu_departamento AS dto ON left(d.c_organigrama,
4)||'000'=dto.c_organigrama
LEFT JOIN procu_departamento AS dir ON left(d.c_organigrama,
3)||'0000'=dir.c_organigrama
LEFT JOIN procu_departamento AS dg ON left(d.c_organigrama,
2)||'00000'=dg.c_organigrama
LEFT JOIN personal.funciones AS pf ON pf.id=da.funcion_id
LEFT JOIN personal.profesiones AS pp ON pp.id=p.id_profesion
WHERE p.apellido ilike '%nuñez%'
ORDER BY "Apellido"

the exact same query with ilike '%NUÑEZ%' works OK...

we are using postgresql 9.1 from ubuntu packages and the database encoding
is (sadly) SQL_ASCII

can anybody point me in the right direction to solve this issue?

--
Roberto Scattini

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Chris Curvey 2013-09-12 21:22:51 Re: problem with query
Previous Message Andreas 'ads' Scherbaum 2013-09-12 20:34:45 Re: [GENERAL] Call for design: PostgreSQL mugs