Hi, can anyone give me a hand? I have the structure below, it returns 4 columns, I need to get 6, but I got lost in the "hit < >"
SELECT DATA,
NVL(
(SELECT MIN(MINTOTIME(BATIDA,':'))
FROM ABATFUN B
WHERE A.CODCOLIGADA=B.CODCOLIGADA
AND A.CHAPA=B.CHAPA
AND A.DATA=B.DATA),'00:00') ENTR1,
NVL(
(SELECT MIN(MINTOTIME(BATIDA,':'))
FROM ABATFUN B
WHERE A.CODCOLIGADA=B.CODCOLIGADA
AND A.CHAPA=B.CHAPA
AND A.DATA=B.DATA
AND BATIDA <>
(SELECT MIN(BATIDA)
FROM ABATFUN B
WHERE A.CODCOLIGADA=B.CODCOLIGADA
AND A.CHAPA=B.CHAPA
AND A.DATA=B.DATA)),'00:00') SAIDA1 ,
NVL(
(SELECT MIN(MINTOTIME(BATIDA,':'))
FROM ABATFUN B
WHERE A.CODCOLIGADA=B.CODCOLIGADA
AND A.CHAPA=B.CHAPA
AND A.DATA=B.DATA
AND BATIDA <>
(SELECT MIN(BATIDA)
FROM ABATFUN B
WHERE A.CODCOLIGADA=B.CODCOLIGADA
AND A.CHAPA=B.CHAPA
AND A.DATA=B.DATA)
AND BATIDA <>
(SELECT MIN(BATIDA)
FROM ABATFUN B
WHERE A.CODCOLIGADA=B.CODCOLIGADA
AND A.CHAPA=B.CHAPA
AND A.DATA=B.DATA
AND BATIDA <>
(SELECT MIN(BATIDA)
FROM ABATFUN B
WHERE A.CODCOLIGADA=B.CODCOLIGADA
AND A.CHAPA=B.CHAPA
AND A.DATA=B.DATA))),'00:00') ENTRADA2 ,
NVL(
(SELECT MIN(MINTOTIME(BATIDA,':'))
FROM ABATFUN B
WHERE A.CODCOLIGADA=B.CODCOLIGADA
AND A.CHAPA=B.CHAPA
AND A.DATA=B.DATA
AND BATIDA <>
(SELECT MIN(BATIDA)
FROM ABATFUN B
WHERE A.CODCOLIGADA=B.CODCOLIGADA
AND A.CHAPA=B.CHAPA
AND A.DATA=B.DATA)
AND BATIDA <>
(SELECT MIN(BATIDA)
FROM ABATFUN B
WHERE A.CODCOLIGADA=B.CODCOLIGADA
AND A.CHAPA=B.CHAPA
AND A.DATA=B.DATA
AND BATIDA <>
(SELECT MIN(BATIDA)
FROM ABATFUN B
WHERE A.CODCOLIGADA=B.CODCOLIGADA
AND A.CHAPA=B.CHAPA
AND A.DATA=B.DATA))
AND BATIDA <>
(SELECT MIN(BATIDA)
FROM ABATFUN B
WHERE A.CODCOLIGADA=B.CODCOLIGADA
AND A.CHAPA=B.CHAPA
AND A.DATA=B.DATA
AND BATIDA <>
(SELECT MIN(BATIDA)
FROM ABATFUN B
WHERE A.CODCOLIGADA=B.CODCOLIGADA
AND A.CHAPA=B.CHAPA
AND A.DATA=B.DATA)
AND BATIDA <>
(SELECT MIN(BATIDA)
FROM ABATFUN B
WHERE A.CODCOLIGADA=B.CODCOLIGADA
AND A.CHAPA=B.CHAPA
AND A.DATA=B.DATA
AND BATIDA <>
(SELECT MIN(BATIDA)
FROM ABATFUN B
WHERE A.CODCOLIGADA=B.CODCOLIGADA
AND A.CHAPA=B.CHAPA
AND A.DATA=B.DATA)))),'00:00') SAIDA2
FROM AAFHTFUN A
WHERE CHAPA = '060637'
AND CODCOLIGADA = '1'
AND DATA >= '21/01/2016'
AND DATA <= '20/02/2016'
- I can not use ROWNUM
because I'm already in a subquery I can not bind the rownum subquery with the 1st table