Обсуждение: BUG #4693: When I use the order by it gets me an error, but if i use it without order by it's a correct query.

The following bug has been logged online:

Bug reference:      4693
Logged by:          Oscar Bejarano
Email address:      obejaranoc@msn.com
PostgreSQL version: 8.3.0
Operating system:   Suse 10
Description:        When I use the order by it gets me an error, but if i
use it without order by it's a correct query.
Details:

=======QUERY=========
select
r1.nombreestado,r1.nombreplantel,r1.clavepsp,r1.primerapellido,r1.segundoape
llido,r1.nombre,r1.sesion,r1.integracion,r1.cantidad_estudiantil,r1.estudian
til,((r1.sesion+r1.integracion+r1.estudiantil)/3) as promedio
    from (
            select
t1.clavepsp,t1.nombreestado,t1.nombreplantel,t1.nombre,t1.primerapellido,t1.
segundoapellido,
                    (select t2.calificacion from tbevaluaciones_terminadas t2 where
t2.evaluacion_tipo='1' and t2.clavepsp=t1.clavepsp)    as sesion,
                    (select t3.calificacion from tbevaluaciones_terminadas t3 where
t3.evaluacion_tipo='3' and t3.clavepsp=t1.clavepsp) as integracion,
                    (select count(t4.clavepsp) from tbevaluaciones_terminadas t4 where
t4.clavepsp=t1.clavepsp and t4.evaluacion_tipo='2') as
cantidad_estudiantil,
                    (select avg(t5.calificacion) from tbevaluaciones_terminadas t5 where
t5.clavepsp=t1.clavepsp and t5.evaluacion_tipo='2') as estudiantil
                    from(
                            select distinct(a.clavepsp),c.nombre as nombreestado,d.nombre as
nombreplantel,a.primerapellido,a.segundoapellido,a.nombre from ctpsps a
                            left join ctestados c on a.claveestado_plantel=c.claveestado
                            left join ctplanteles d on a.claveplantel=d.claveplantel
                            where 1=1 and a.clavepsp in (select b.clavepsp from
tbevaluaciones_terminadas b where 1=1 and b.fecha <='31/12/2008')
                            order by 1,2,3,4,5,6

                        ) t1

        ) r1 order by 1,2,3,4,5,6

================

Thanks,
Oscar

On Wed, Mar 4, 2009 at 2:05 PM, Oscar Bejarano <obejaranoc@msn.com> wrote:
>
> The following bug has been logged online:
>
> Bug reference: =A0 =A0 =A04693
> Logged by: =A0 =A0 =A0 =A0 =A0Oscar Bejarano
> Email address: =A0 =A0 =A0obejaranoc@msn.com
> PostgreSQL version: 8.3.0
> Operating system: =A0 Suse 10
> Description: =A0 =A0 =A0 =A0When I use the order by it gets me an error, =
but if i
> use it without order by it's a correct query.
> Details:
>
> =3D=3D=3D=3D=3D=3D=3DQUERY=3D=3D=3D=3D=3D=3D=3D=3D=3D

which ORDER BY is causing troubles to you? the one in ther outer query
or the one in the inner query?
can you provide at least the error message? a little test case would be use=
ful

--=20
Atentamente,
Jaime Casanova
Soporte y capacitaci=F3n de PostgreSQL
Asesor=EDa y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157