Skip site navigation (1) Skip section navigation (2)

Re: Referencia cruzada

From: Dan <danstreet(at)gmail(dot)com>
To: Jaime Casanova <jaime(at)2ndquadrant(dot)com>
Cc: pgsql-es-ayuda(at)postgresql(dot)org
Subject: Re: Referencia cruzada
Date: 2010-05-21 17:53:40
Message-ID: AANLkTinxwOgnJ_ivzVHeR_VaFOpxv_K0FktMh6PXugOm@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-es-ayuda
Buenas Tardes, lo del crosstab ya 'funciona'  pero tengo un inconveniente:
1- cuando consulto por un solo idchofer sale correcto, idchofer, hora de
ingreso sale la minima, hora de salida sale la maxima
2.- cuando consulto todos los choferes, como esta en el sql adjunto, algunos
valores salen invertidos la hora minima osea de ingreso sale despues,
y la hora maxima de salida sale antes.

como les comente en la tabla control un chofer puede registrar varios
ingresos y salidas
y si notan en la consulta que realizo primero busco la menor hora de ingreso
y en el 2do query despues de la union busco la hora maxima

SELECT *
FROM crosstab('
select cast(c.idchofer, c.fecha, min(c.hora1) as hora
from control c
where c.fecha = ''20/05/2010'' and c.tipo=''INGRESO''
group by tio.pers_id, tio.fecha
   union all
select cast(c.idchofer, c.fecha, max(c.hora1) as hora
   from control
   where c.fecha = ''20/05/2010'' and c.tipo=''SALIDA''
   group by c.idchofer, c.fecha
   order by 1,2'
)
AS control(idchofer integer, ingreso time, salida time);



On Fri, May 21, 2010 at 10:54 AM, Dan <danstreet(at)gmail(dot)com> wrote:

> Upsss... Gracias a todos, no me habia percatado de ejecutar make y make
> install en el directorio */usr/src/postgresql-8.2.4/contrib/tablefunc/
> y luego
>
> *
> path\to\postgresql\bin\psql -h localhost -U someuser -d somedb -f
> "path\to\postgresql\share\contrib\tablefunc.sql"
>
> Slds.
>
> On Fri, May 21, 2010 at 12:27 AM, Dan <danstreet(at)gmail(dot)com> wrote:
>
>> Hola Jaime, busque acerca de crosstab y encontre la sgt pagina:
>> http://www.postgresonline.com/journal/index.php?/archives/14-guid.html
>>
>> <http://www.postgresonline.com/journal/index.php?/archives/14-guid.html>el
>> paso que indica: path\to\postgresql\bin\psql -h localhost -U someuser -d
>> somedb -f "path\to\postgresql\share\contrib\tablefunc.sql"
>> en el postgresql 8.3 funciono perfecto, pero en el 8.2.4 me sale el sgt
>> mensaje:
>>
>>  *HINT:  No function matches the given name and argument types. You may
>> need to add explicit type casts.*
>> *-- recursion detection*
>> *INSERT INTO connectby_int VALUES(10,9);*
>> *INSERT 0 1*
>> *INSERT INTO connectby_int VALUES(11,10);*
>> *INSERT 0 1*
>> *INSERT INTO connectby_int VALUES(9,11);*
>> *INSERT 0 1*
>> *-- should fail due to infinite recursion*
>> *SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2',
>> 0, '~') AS t(keyid int, parent_keyid int, level int, branch text);*
>> *psql:/usr/src/postgresql-8.2.4/contrib/tablefunc/sql/tablefunc.sql:178:
>> ERROR:  function connectby("unknown", "unknown", "unknown", "unknown",
>> integer, "unknown") does not exist*
>> *LINE 1: SELECT * FROM connectby('connectby_int', 'keyid', 'parent_ke...*
>> *                      ^*
>> *HINT:  No function matches the given name and argument types. You may
>> need to add explicit type casts.*
>> *-- infinite recursion failure avoided by depth limit*
>> *SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2',
>> 4, '~') AS t(keyid int, parent_keyid int, level int, branch text);*
>> *psql:/usr/src/postgresql-8.2.4/contrib/tablefunc/sql/tablefunc.sql:181:
>> ERROR:  function connectby("unknown", "unknown", "unknown", "unknown",
>> integer, "unknown") does not exist*
>> *LINE 1: SELECT * FROM connectby('connectby_int', 'keyid', 'parent_ke...*
>> *                      ^*
>> *HINT:  No function matches the given name and argument types. You may
>> need to add explicit type casts.*
>> *-- test for falsely detected recursion*
>> *DROP TABLE connectby_int;*
>> *DROP TABLE*
>> *CREATE TABLE connectby_int(keyid int, parent_keyid int);*
>> *CREATE TABLE*
>> *INSERT INTO connectby_int VALUES(11,NULL);*
>> *INSERT 0 1*
>> *INSERT INTO connectby_int VALUES(10,11);*
>> *INSERT 0 1*
>> *INSERT INTO connectby_int VALUES(111,11);*
>> *INSERT 0 1*
>> *INSERT INTO connectby_int VALUES(1,111);*
>> *INSERT 0 1*
>> *-- this should not fail due to recursion detection*
>> *SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '11',
>> 0, '-') AS t(keyid int, parent_keyid int, level int, branch text);*
>> *psql:/usr/src/postgresql-8.2.4/contrib/tablefunc/sql/tablefunc.sql:191:
>> ERROR:  function connectby("unknown", "unknown", "unknown", "unknown",
>> integer, "unknown") does not exist*
>> *LINE 1: SELECT * FROM connectby('connectby_int', 'keyid', 'parent_ke...*
>> *                      ^*
>> *HINT:  No function matches the given name and argument types. You may
>> need to add explicit type casts.*
>>
>> 2010/5/20 Jaime Casanova <jaime(at)2ndquadrant(dot)com>
>>
>> 2010/5/20 Dan <danstreet(at)gmail(dot)com>:
>>> >
>>> >        dias                  19/05/2010   20/05/2010
>>> > -------------------------------------------------------------------
>>> > 1 | Juan Perez       | 08:00-16:00 | 08:02-16:45
>>> > 2 | Luis Rodriguez  | 08:05-16:30 | 08:05-18:00
>>> > 3 | Damian Diaz     | 08:06-16:00 | 08:00-17:00
>>> >
>>> >
>>>
>>> para hacerlo tal como esta en tu ejemplo necesitaras usar crosstab
>>>
>>> --
>>> Jaime Casanova         www.2ndQuadrant.com
>>> Soporte y capacitaciĆ³n de PostgreSQL
>>>
>>
>>
>>
>> --
>> -----------------
>>  [) /-\  |\|
>>
>
>
>
> --
> -----------------
>  [) /-\  |\|
>



-- 
-----------------
 [) /-\  |\|

In response to

Responses

pgsql-es-ayuda by date

Next:From: DanDate: 2010-05-21 18:12:56
Subject: Re: Referencia cruzada
Previous:From: DanDate: 2010-05-21 15:54:07
Subject: Re: Referencia cruzada

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group