title: Oracle select one row in join and subquery inside a decode function
body: <p>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).</p>
  
  <pre><code>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         |
      -----------------------------------------------------------------------------------
  </code></pre>
  
  <p>in my query i want to export the number of events completed or incomplete (depending the value PORCENTAJE in EVENTS_ELEMENTS).</p>
  
  <p>First I used this Query to obtain the last row of every EVENT:</p>
  
  <pre><code>SELECT *
  FROM EVENTS eve, EVENTS_ELEMENTS el
  WHERE eve.ID(+) = el.ID_EVENTO and ROWNUM = 1
  ORDER BY ID desc
  </code></pre>
  
  <p>trying to obtain this result:</p>
  
  <pre><code>---------------------------------------------------------------------------------------------------------------------
  | 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         |
  ---------------------------------------------------------------------------------------------------------------------
  </code></pre>
  
  <p>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.</p>
  
  <pre><code>---------------------------------------------------------------------------------------------------------------------
  | 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         |
  ---------------------------------------------------------------------------------------------------------------------
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>---------------------------------
  | LABEL    | SERIE      | VALUE |
  ---------------------------------
  | COMPLETO | Serie-Text | 1     |
  | EJECUCION| Serie-Text | 1     |
  ---------------------------------
  if PORCENTAJE == 100 -&gt; COMPLETO
  if PORCENTAJE != 100 -&gt; EJECUCION
  </code></pre>
  
  <p>I suppose the second Query should be:</p>
  
  <pre><code>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
  </code></pre>