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
| CREATE OR REPLACE FORCE VIEW COSWIN.VIST_DATA
(EQUIPMENT, DESCRIPTION, LIGNE, ZONE, CRITIQUE, FONCTION, STATUS, LEVEL_, DATE_
, NUM_SEMAINE, NUM_MOIS, NUM_ANNEE, T_OUVERTURE, N_OT, T_ARRET) AS
SELECT E.EREQ_CODE AS EQUIPMENT,
E.EREQ_description AS DESCRIPTION,
E.EREQ_PARENT_EQUIPMENT AS LIGNE,
E.EREQ_ZONE AS ZONE,
E.EREQ_boolean1 AS CRITIQUE,
E.EREQ_CATEGORY AS FONCTION,
E.EREQ_EQUIPMENT_STATUS AS STATUS,
'EQUIPMENT' AS LEVEL_,
EF.EQFR_DATE1 AS DATE_,
TO_NUMBER (TO_CHAR (EF.EQFR_DATE1, 'WW')) AS NUM_SEMAINE,
TO_NUMBER (TO_CHAR (EF.EQFR_DATE1, 'MM')) AS NUM_MOIS,
TO_NUMBER (TO_CHAR (EF.EQFR_DATE1, 'YYYY')) AS NUM_ANNEE,
EF.EQFR_NUMBER1 AS T_OUVERTURE,
EF.EQFR_NUMBER2 AS N_OT,
EF.EQFR_NUMBER3 AS T_ARRET
FROM EQUIPMENT E
INNER JOIN EQUIPMENT_FREE EF
ON E.EREQ_CODE=EF.EQFR_EQUIPMENT
WHERE E.EREQ_ENTITY='TPM'
AND E.EREQ_EQUIPMENT_STATUS='N'
AND E.EREQ_LEVEL=2 /*2 = 'EQUIPMENT' */
AND EF.EQFR_DATE1 >TO_DATE ('12/31/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
ORDER BY EQUIPMENT, DATE_ desc;
/
SELECT LIGNE , count(*) as "Nb lignes pas ligne de production"
FROM COSWIN.VIST_DATA
GROUP BY LIGNE;
/
CREATE OR REPLACE FORCE VIEW "COSWIN"."VIST_LIGN" ("LIGNE", "DATE_", "TO_LIGNE", "NB_LIGNE") AS
/*SELECT ligne,DATE_, sum(TO_LIGNE) TO_LIGNE, sum(NB) NB_LIGNE
FROM
(*/ SELECT ligne, date_, MAX(t_ouverture) TO_LIGNE, count(*) NB
FROM vist_data
GROUP BY ligne, date_
/*)
GROUP BY ligne,DATE_*/
ORDER BY date_ DESC;
/
SELECT LIGNE , count(*) as "Nb lignes pas ligne de production"
from COSWIN.VIST_LIGN
GROUP BY LIGNE;
/
SELECT l.ligne,d.EQUIPMENT,d.DESCRIPTION,d.LIGNE,d.ZONE,d.T_OUVERTURE, d.N_OT, l.to_ligne, d.date_
FROM vist_data d
INNER JOIN vist_lign l
ON d.LIGNE = l.ligne
AND d.date_ = l.date_
WHERE d.date_ > TO_DATE ('05/07/2019 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
AND d.ligne IN ('CMS-LIGN-02','CMS-LIGN-05') |
