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
| SQL> CREATE OR REPLACE VIEW ME_V_SUP_R AS
2 SELECT DISTINCT
3 ME_SUPPORT.SUP_IDENT,
4 ME_SUPPORT.CO_NUMINSEE,
5 ME_SUPPORT.SUP_LTY_IDENT,
6 ME_SUP_LTY.SUP_LTY_LIBELLE,
7 ME_SUPPORT.SUP_LTE_IDENT,
8 ME_SUPPORT.SUP_LPR,
9 ME_SUPPORT.SUP_DATE,
10 COUNT(ME_SUP_REL.SUP_IDENT) RELATION,
11 ME_SUPPORT.LECH_ECHSAISIE,
12 ME_PDT.PDT_IDENT,
13 ME_SUP_LP.SUP_PEINT_LIBELLE,
14 DECODE(COUNT(ME_SUP_REL.SUP_IDENT),'1',(MIN(ME_SUP_REL.RSU_SUP_NUMERO)||' ; '||MAX(ME_SUP_REL.RSU_SUP_NUMERO)),ME_SUP_REL.RSU_SUP_NUMERO)
15 FROM
16 ME_SUPPORT,
17 ME_SUP_REL,
18 ME_SUP_LTY,
19 ME_PDT,
20 ME_SUP_LP
21 WHERE ME_SUPPORT.SUP_IDENT = ME_PDT.SUP_IDENT(+)
22 AND ME_SUPPORT.SUP_IDENT = ME_SUP_REL.SUP_IDENT
23 AND ME_SUPPORT.SUP_LTY_IDENT = ME_SUP_LTY.SUP_LTY_IDENT
24 AND ME_SUP_LP.SUP_PEINT_IDENT(+) = ME_SUPPORT.SUP_PEINT_IDENT
25 GROUP BY
26 ME_SUPPORT.SUP_IDENT,
27 ME_SUPPORT.CO_NUMINSEE,
28 ME_SUPPORT.SUP_LTY_IDENT,
29 ME_SUP_LTY.SUP_LTY_LIBELLE,
30 ME_SUPPORT.SUP_LTE_IDENT,
31 ME_SUPPORT.SUP_LPR,
32 ME_SUPPORT.SUP_DATE,
33 ME_SUPPORT.LECH_ECHSAISIE,
34 ME_PDT.pdt_ident,
35 ME_SUP_LP.SUP_PEINT_LIBELLE; |