[CASE] et [ORDER BY] prb

Bonjour
je travaille avec oracle 10g et j'ai la procedure suivante

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
 
CREATE OR REPLACE PROCEDURE sp_getAlarmDateList
(
	RCT1      IN  OUT 	GLOBALPKG.RCT1
)
AS
BEGIN
 
	OPEN RCT1 FOR
	SELECT DISTINCT
 
	case
	 when (TO_NUMBER(TO_CHAR(alarmDate, 'MM'))  < 10) then '01/0' || CAST(TO_NUMBER(TO_CHAR(alarmDate, 'MM')) as VARCHAR2 (30)) || '/' || CAST(TO_NUMBER(TO_CHAR(alarmDate, 'YYYY')) as VARCHAR2 (30))
	 else '01/' || CAST(TO_NUMBER(TO_CHAR(alarmDate, 'MM')) as VARCHAR2 (30)) || '/' || CAST(TO_NUMBER(TO_CHAR(alarmDate, 'YYYY')) as VARCHAR2 (30))
	end as alarmDate,
 
	case
	 when (TO_NUMBER(TO_CHAR(alarmDate, 'MM'))  < 10) then '0' || CAST(TO_NUMBER(TO_CHAR(alarmDate, 'MM')) as VARCHAR2 (30)) || '/' || CAST(TO_NUMBER(TO_CHAR(alarmDate, 'YYYY')) as VARCHAR2 (30))
	 else CAST(TO_NUMBER(TO_CHAR(alarmDate, 'MM')) as VARCHAR2 (30)) || '/' || CAST(TO_NUMBER(TO_CHAR(alarmDate, 'YYYY')) as VARCHAR2 (30))
	 end as alarmDate
 
	FROM  tbl_Nurse_Alarm
	ORDER BY alarmDate DESC;
END;
/

j'ai l'erreur suivante
PL/SQL: ORA-00960: nom de colonne ambigu dans liste de sélection

au niveau du ligne

Code:

ORDER BY alarmDate DESC;

merci de m'aider

OPEN RCT1 FOR SELECT DISTINCT case when (TO_NUMBER(TO_CHAR(alarmDate, 'MM')) < 10) then '01/0' || CAST(TO_NUMBER(TO_CHAR(alarmDate, 'MM')) as VARCHAR2 (30)) || '/' || CAST(TO_NUMBER(TO_CHAR(alarmDate, 'YYYY')) as VARCHAR2 (30)) else '01/' || CAST(TO_NUMBER(TO_CHAR(alarmDate, 'MM')) as VARCHAR2 (30)) || '/' || CAST(TO_NUMBER(TO_CHAR(alarmDate, 'YYYY')) as VARCHAR2 (30)) end as alarmDate, case when (TO_NUMBER(TO_CHAR(alarmDate, 'MM')) < 10) then '0' || CAST(TO_NUMBER(TO_CHAR(alarmDate, 'MM')) as VARCHAR2 (30)) || '/' || CAST(TO_NUMBER(TO_CHAR(alarmDate, 'YYYY')) as VARCHAR2 (30)) else CAST(TO_NUMBER(TO_CHAR(alarmDate, 'MM')) as VARCHAR2 (30)) || '/' || CAST(TO_NUMBER(TO_CHAR(alarmDate, 'YYYY')) as VARCHAR2 (30)) end as alarmDate2 FROM tbl_Nurse_Alarm ORDER BY 1 DESC