Problema con group by!!

[matanga]

Hola,

Tienes razon, no vi el MAX dentro del SELECT, mis disculpas.

1. El error se da porque falta agregar el campo GEN005.tipo_evento al GROUP BY, pero tengo la sospecha que si lo haces, no obtendras el resultado esperado.

2. Creo que esta consulta puede funcionar, debes probarla bien, como no tengo tus tablas ni datos, no puedo asegurarte que funciona.

Código:

SELECT
SP3.PERSONTABLE.FIRSTNAME || ' ' || SP3.PERSONTABLE.LASTNAME,
SP3.DEPARTMENTTABLE.DESCRIPTION,

case
when (
((to_number(substr(to_char(BADGEHISTORYTABLE2.microdate,'hh24:mi:ss'),1,2))*60)+
to_number(substr(to_char(BADGEHISTORYTABLE2.microdate,'hh24:mi:ss'),4,2))) > 520 and 
((to_number(substr(to_char(BADGEHISTORYTABLE2.microdate,'hh24:mi:ss'),1,2))*60)+
to_number(substr(to_char(BADGEHISTORYTABLE2.microdate,'hh24:mi:ss'),4,2))) < 740 and 
GEN005.tipo_evento = 'SAL'
) then 1
else 0
end

FROM

(select SP3.BADGEHISTORYTABLE.EMPLOYEENUMBER, max(SP3.BADGEHISTORYTABLE.EMPLOYEENUMBER.microdate) microdate
  from SP3.BADGEHISTORYTABLE
 group by SP3.BADGEHISTORYTABLE.EMPLOYEENUMBER ) BADGEHISTORYTABLE2,

SP3.BADGEHISTORYTABLE,
SP3.PERSONTABLE,
SP3.DEPARTMENTTABLE,
GEN005,
GEN004

WHERE

BADGEHISTORYTABLE2.EMPLOYEENUMBER = SP3.BADGEHISTORYTABLE.EMPLOYEENUMBER and
BADGEHISTORYTABLE2.microdate = SP3.BADGEHISTORYTABLE.microdate and

( GEN004.COD_SEDE=GEN005.COD_SEDE )
AND ( SP3.BADGEHISTORYTABLE.EMPLOYEENUMBER=SP3.PERSONTAB LE.EMPLOYEENUMBER )
AND ( GEN005.COD_EVENTO=SP3.BADGEHISTORYTABLE.READERID )
AND ( SP3.DEPARTMENTTABLE.ID=SP3.PERSONTABLE.DEPARTMENTI D )
AND (
( ( SP3.BADGEHISTORYTABLE.MICRODATE ) BETWEEN to_date('10/10/2007 00:00:00','dd/mm/yyyy hh24:mi:ss') AND to_date('11/10/2007 00:00:00','dd/mm/yyyy hh24:mi:ss') )
AND ( ( GEN004.DESC_SEDE ) in ('Santa Paula') )
AND ( ( SP3.DEPARTMENTTABLE.DESCRIPTION ) in ('HF510') )
AND (to_char (SP3.BADGEHISTORYTABLE.MICRODATE, 'D') != '7'
OR to_char (SP3.BADGEHISTORYTABLE.MICRODATE, 'D') != '1')
AND (( GEN005.TIPO_EVENTO = 'SAL' )
OR ( GEN005.TIPO_EVENTO = 'ENT' ))
)

Saludos.