Re: Referencia cruzada

Ok, gracias nuevamente, ya lo solucione, dentro del sql el order lo
modifique por: order by 2,1,3'

Slds

On Fri, May 21, 2010 at 12:53 PM, Dan <danstreet(at)gmail(dot)com> wrote:

> 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
>>>>
>>>
>>>
>>>
>>> --
>>> -----------------
>>> [) /-\ |\|
>>>
>>
>>
>>
>> --
>> -----------------
>> [) /-\ |\|
>>
>
>
>
> --
> -----------------
> [) /-\ |\|
>

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