Discussion :

[you98]

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

ORDER BY alarmDate DESC;

merci de m'aider

[Garuda]

Tu ne peux pas avoir deux fois le même nom de colonne dans un select (en l'occurence alarmedate)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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

PS : je pense que tu peux remplacer tous tes 'CAST' par de simples fonctions date (round et trunc)