Oracle Subquery (bring the 6 smallest ones)

Asked

Viewed 78 times

0

Hi someone can give me a hand? I have the structure below, it returns 4 columns, I need to keep 6, but I got lost in "beat <>"

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’t use ROWNUM due to already being in a subquery I have no way to tie the subquery of rownum with the 1st table

oracle

  • you can do something like that

  • you can do something like this in subquery .... (select value from (select value from table order by 1) Where rownum < 6) or try to use Analytic Function http://www.oracle.com/technetwork/issue-archive/2013/13-may/o33sql-1917326.html

  • The problem is that my line is already a subquery where I have to tie tables A with B, in your example it would be there where this written table, only that when you put the error there not recognizing A.Table ...that in case A.CODCOLIGADA=B.CODCOLIGADA AND A.CHAPA=B.CHAPA AND A.DATA=B.DATA (nothing this with A is recognized), so I commented Query, ((Subquery), Subquery Subquery) (that Query cannot be tied with Sub)

  • Do so @Samuel, forget sql, publish the structure of the sample data tables and the expected result.

No answers

Browser other questions tagged oracle

You are not signed in. Login or sign up in order to post.