ORA-00921: unexpected end of SQL command

[Mykhell]

Voici ma requête
et franchement, c'est peut-etre la fatigue, mais je vois pas mon SELECT
l.description,
(
SELECT a1.first_name||' '||a1.last_name
FROM agents a1
WHERE a1.birth_date =
(SELECT MAX(a.birth_date) FROM agents a WHERE a.location_id = l.location_id )
),

(
SELECT a2.first_name||' '||a2.last_name
FROM agents a2
WHERE a2.birth_date =
(SELECT MIN(a.birth_date) FROM agents a WHERE a.location_id = l.location_id)
FROM
locations l
;

[Waldar]

Vous ne pouvez pas faire de requête scalaire qui renvoie plus d'une ligne.
Comme vous cherchez par location mais pour toutes les locations, ça ne fonctionne pas.

Essayez ceci :

Code :

1
2
3
4
5
6
7
8
SELECT DISTINCT
    l.description,
    max(a.first_name || ' ' || a.last_name) KEEP (DENSE_RANK LAST  ORDER BY birth_date ASC) OVER (PARTITION BY l.location_id) AS agent_jeune,
    max(a.first_name || ' ' || a.last_name) KEEP (DENSE_RANK FIRST ORDER BY birth_date ASC) OVER (PARTITION BY l.location_id) AS agent_vieux
FROM
    locations l
    INNER JOIN agents a
      ON a.location_id = l.location_id