Bonjour,

J'ai créé une requete SQL qui fonctionne très bien dans sqldevelopper et me donne le résultat attendu.
Lorsque je j'exécute cette requête via une commande sqlplus (dans une console DOS), je n'ai aucun résultat.
Cette requete est sensée extraire les données et les écrire dans un fichier CSV.
ci-dessous la requete. Au passage si vous pensez que la requete est mal construite, je suis preneur de vos remarques.

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
SET NEWPAGE 0
SET LINESIZE 1024
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET MARKUP HTML OFF SPOOL OFF
SET TRIMSPOOL ON
SET COLSEP ';'
 
-- Formattage des colonnes
COLUMN ReferenceECR format A12;
COLUMN EtatWF format A15;
COLUMN NoeudCCB1 format A20;
COLUMN NoeudCCB2 format A20;
 
spool C:\temp\Statut_ECR.csv
 
-- Creation ligne d'entête
select 'Reference ECR','Date de creation','Etat WF','CCB1','date reception CCB1','date de reponse CCB1','delai CCB1',
'CCB2','date reception CCB2','date de reponse CCB2','delai CCB2','date de fin WF' from dual;
-- Extraction des données
select CCB1.processCCB1 ReferenceECR, CCB1.DebutProcess Creation, 
(select description from SNE_R19.tn_flow_process_state where object_id=CCB1.StatutProcess) EtatWF,
CCB1.NoeudCCB1 NoeudCCB1,
CCB1.ReceptionCCB1 ReceptionCCB1, CCB1.ReponseCCB1 ReponseCCB1,CCB1.tempsCCB1 TraitementCCB1,
CCB2.NoeudCCB2 NoeudCCB2,
CCB2.ReceptionCCB2 ReceptionCCB2, CCB2.ReponseCCB2 ReponseCCB2,CCB2.tempsCCB2 TraitementCCB2,
case when CCB1.StatutProcess<>2 then null
else CCB1.FinProcess
end ClotureECR
from 
(select P1.tdm_name processCCB1,
P1.TDMX_FLOW_PROCESS_STATUS StatutProcess,
P1.creation_date DebutProcess,
H1.TDM_RECEIVE_TIME ReceptionCCB1,
N1.MODIFICATION_DATE ReponseCCB1,
CASE WHEN TRUNC((N1.MODIFICATION_DATE - H1.TDM_RECEIVE_TIME),0)>1 then
TO_CHAR(TRUNC((N1.MODIFICATION_DATE - H1.TDM_RECEIVE_TIME),0),'9999') || ' j ' ||
TO_CHAR(TRUNC((N1.MODIFICATION_DATE - H1.TDM_RECEIVE_TIME)*24,0)-(TRUNC((N1.MODIFICATION_DATE - H1.TDM_RECEIVE_TIME),0)*24),'99') || ' h '
ELSE
TO_CHAR(TRUNC((N1.MODIFICATION_DATE - H1.TDM_RECEIVE_TIME)*24,0),'99') || ' h ' 
end tempsCCB1,
N1.tdm_description NoeudCCB1, 
P1.modification_date FinProcess
from SNE_R19.tdm_sf_process P1
inner join SNE_R19.tdm_sf_prc_history H1 on P1.object_id=H1.tdm_process_id
inner join SNE_R19.tdm_sf_node N1 on H1.object_id1=N1.object_id and N1.tdm_description like 'Début'
where P1.tdm_name like 'ECR%') CCB1
left join  
(select P2.tdm_name processCCB2,
H2.TDM_RECEIVE_TIME ReceptionCCB2,
N2.MODIFICATION_DATE ReponseCCB2,
case when TRUNC((N2.MODIFICATION_DATE - H2.TDM_RECEIVE_TIME),0)>1 then
TO_CHAR(TRUNC((N2.MODIFICATION_DATE - H2.TDM_RECEIVE_TIME),0),'9999') || ' j ' ||
TO_CHAR(TRUNC((N2.MODIFICATION_DATE - H2.TDM_RECEIVE_TIME)*24,0)-(TRUNC((N2.MODIFICATION_DATE - H2.TDM_RECEIVE_TIME),0)*24),'99') || ' h '
else
TO_CHAR(TRUNC((N2.MODIFICATION_DATE - H2.TDM_RECEIVE_TIME)*24,0),'99') || ' h '
end tempsCCB2,
N2.tdm_description NoeudCCB2
from SNE_R19.tdm_sf_process P2
inner join SNE_R19.tdm_sf_prc_history H2 on P2.object_id=H2.tdm_process_id
inner join SNE_R19.tdm_sf_node N2 on H2.object_id1=N2.object_id and N2.tdm_description like 'Planifier ECO%'
where P2.tdm_name like 'ECR%') CCB2 On CCB1.processCCB1=CCB2.processCCB2
where CCB1.StatutProcess<>3
order by CCB1.processCCB1;
 
spool off
exit