1
RESOLVED
I solved as follows, I created a view equal to my table of service orders more that was ordered by the team and by the priority, and then I kept the query above by only changing the table of the subquery that I put the view that I created, so I don’t have to worry about the ordering because the oracle will bring the way the view brings and the view already brings ordered so gave straight, at least for now... thank you so much for everyone who tried to help and if by chance you know a better way to solve my problem do not fail to answer me :D
########################################################################I found some questions around here similar to my most none of the answers helped me, I have the following situation:
A table with orders of service, imagine that it has only 5 fields that are: ORDEM_ID | EQUIPE_MANUT | DATA_ABERTURA | PRIORI | OBSERVATION.
And I need to set up a web panel that will display the 5 O.S with each team’s lowest PRIORI value.
ex:
ORDEM_ID | EQUIPE_MANUT | DATA_ABERTURA | PRIORI | OBSERVACAO
0001 100 01/01/2017 1 aaaa
0002 100 01/01/2017 2 aaaa
0003 100 01/01/2017 3 aaaa
0004 100 01/01/2017 4 aaaa
0005 100 01/01/2017 5 aaaa
0006 105 01/01/2017 1 aaaa
0007 105 01/01/2017 2 aaaa
0008 105 01/01/2017 3 aaaa
0009 105 01/01/2017 4 aaaa
0010 105 01/01/2017 5 aaaa
Well I managed to list only 5 O.S of each EQUIPE_MANUT the problem starts when I try to return the 5 with the lowest PRIORI value, I will show the query I have used:
SELECT ORDEM_ID AS ORDEM,
DATA_ABERTURA AS DATA_ABERTURA,
C1.EQUIPE_MANUT AS EQUIPE,
TRANSLATE(C1.OBSERVACAO,'âàãáÁÂÀÃéêÉÊíÍóôõÓÔÕüúÜÚÇç','AAAAAAAAEEEEIIOOOOOOUUUUCC') AS OBSERVACAO
FROM CORDEMSERV C1
WHERE C1.ORDEM_ID IN
(SELECT C2.ORDEM_ID
FROM CORDEMSERV C2
WHERE C2.EQUIPE_MANUT = C1.EQUIPE_MANUT
AND C2.STATUS ='1'
AND C2.PRIO IS NOT NULL
AND ROWNUM <=5
)
AND C1.STATUS='1'
AND C1.PRIO IS NOT NULL
ORDER BY C1.EQUIPE_MANUT;
I have tried to put this subquery inside another subquery without rownum and use itlo after the most ordered data ai i can not connect C1.EQUIPE_MANUT with C2.EQUIPE_MANUT I think that due to be a subquery that already inside another subquery ... good is that if someone can help me :D
this query works, so I understood only lack the correct ordering, right?
– Damon Dudek
yes, just missing the sort, but the oracle does not allow me to place an order by there in the subquery...
– Edilson Pereira