Oracle select one row in join and subquery inside a decode function

Question

I have two Oracle(9i) tables (EVENTS and EVENTS_ELEMENTS). The TABLE EVENTS has a PK (ID) associated to ID_EVENTO as FK (EVENTS_ELEMENTS).

Table EVENTS
    ----------------------------------
    | ID | TIPO | FECHAINICIO        |
    ----------------------------------
    | 23 | real | dd/mm/yyyy hh:mm:ss|
    | 97 | real | dd/mm/yyyy hh:mm:ss|
    ----------------------------------

Table EVENTS_ELEMENTs
    -----------------------------------------------------------------------------------
    | ID_EVENTO | FIN_PREVISTO        | FIN_REAL           | PORCENTAJE | ID_ELEMENTO |
    -----------------------------------------------------------------------------------
    | 23        | dd/mm/yyyy hh:mm:ss |dd/mm/yyyy hh:mm:ss | 100        | 731         |
    | 23        | dd/mm/yyyy hh:mm:ss |dd/mm/yyyy hh:mm:ss | 100        | 732         |
    | 23        | dd/mm/yyyy hh:mm:ss |dd/mm/yyyy hh:mm:ss | 73         | 733         |
    | 97        | dd/mm/yyyy hh:mm:ss |dd/mm/yyyy hh:mm:ss | 100        | 840         |
    | 97        | dd/mm/yyyy hh:mm:ss |dd/mm/yyyy hh:mm:ss | 100        | 841         |
    -----------------------------------------------------------------------------------

in my query i want to export the number of events completed or incomplete (depending the value PORCENTAJE in EVENTS_ELEMENTS).

First I used this Query to obtain the last row of every EVENT:

SELECT *
FROM EVENTS eve, EVENTS_ELEMENTS el
WHERE eve.ID(+) = el.ID_EVENTO and ROWNUM = 1
ORDER BY ID desc

trying to obtain this result:

---------------------------------------------------------------------------------------------------------------------
| ID | TIPO | FECHAINICIO        |  ID_EVENTO | FIN_PREVISTO        | FIN_REAL           | PORCENTAJE | ID_ELEMENTO |
---------------------------------------------------------------------------------------------------------------------
| 23 | real | dd/mm/yyyy hh:mm:ss|  23        | dd/mm/yyyy hh:mm:ss |dd/mm/yyyy hh:mm:ss | 73         | 733         |
| 97 | real | dd/mm/yyyy hh:mm:ss|  97        | dd/mm/yyyy hh:mm:ss |dd/mm/yyyy hh:mm:ss | 100        | 841         |
---------------------------------------------------------------------------------------------------------------------

but my result is only one row with EVENT, not every EVENT with the last EVENTS_ELEMENTS row with the PORCENTAJE value what i need to calculate what EVENTS are completed and what are un execution.

---------------------------------------------------------------------------------------------------------------------
| ID | TIPO | FECHAINICIO        |  ID_EVENTO | FIN_PREVISTO        | FIN_REAL           | PORCENTAJE | ID_ELEMENTO |
---------------------------------------------------------------------------------------------------------------------
| 23 | real | dd/mm/yyyy hh:mm:ss|  23        | dd/mm/yyyy hh:mm:ss |dd/mm/yyyy hh:mm:ss | 73         | 733         |
---------------------------------------------------------------------------------------------------------------------

After this, i want to count the number of completed and no-completed. Im trying to understand the DECODE function. In this example i try to get the result:

---------------------------------
| LABEL    | SERIE      | VALUE |
---------------------------------
| COMPLETO | Serie-Text | 1     |
| EJECUCION| Serie-Text | 1     |
---------------------------------
if PORCENTAJE == 100 -> COMPLETO
if PORCENTAJE != 100 -> EJECUCION

I suppose the second Query should be:

SELECT DECODE (EVENTS,
                  (SELECT count(*)
               FROM [Query of first question]), 'COMPLETO',
              (SELECT count(*)
               FROM [Similar Query of first question]), 'EJECUCION') as LABEL,
    'Serie-Text' as SERIE,
    count(ev.ID) as VALUE
FROM EVENTS ev, EVENTS_ELEMENTS el
WHERE ev.ID(+) = el.ID_EVENTO

Answer 1

I think this query fits your needs:

select ev.ID, ev.TIPO, evFECHAINICIO,
       decode(ev.PORCENTAJE, 100, 'COMPLETED', 'IN PROGRESS') status, count(*) nb
from EVENTS_ELEMENTS el, EVENTS ev
where ev.ID = el.ID_EVENTO
group by ev.ID, ev.TIPO, evFECHAINICIO,
      decode(ev.PORCENTAJE, 100, 'COMPLETED', 'IN PROGRESS')
;

I group by ID and status, status being computed thanks to the decode function: if percentage is 100, status is completed, else it is in progress.