Bonjour,

Le sujet du topic est on ne peut plus classique mais j'en ai mal au crane tellement cette requête m'a pris la tête.


Il s'agit d'une requête ramenant des compteurs d'eau pour des logements, le tarrif qui est appliqué à la consomation d'eau pour une période, ainsi que la relevé compteur pour la période.

La voici donc :
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
SELECT ROWNUM "NUMLIGNE",
       KCPCPT.LKLO "MODULE",
       TO_CHAR(KCNTLOC.CDECL,'DD-MM-YYYY')  "ENTREE",
       TO_CHAR(KCNTLOC.CDSCL,'DD-MM-YYYY') "SORTIE",
       KCPIND.CNCT "CONTRAT",
       KCNT.CNDCT "LOCATAIRE",
       KCPCPT.CPIDF "NUM CPT", 
       DECODE(KCPCPT.TCTC,'F','F FROID','C','C CHAUD') "TYPE CPT",
       DECODE(KCPIND.COACTIF,0,'HISTORIEE',1,'A TRAITER',KCPIND.COACTIF) "ETAT CONSO",
       TO_CHAR(KCPIND.CODP,'YYYY-MM-DD') "DATE RELEVE",
       DECODE(NVL(KCPIND.CONM,0),0,KCPIND.CONI,KCPIND.CONM) "INDEX RELEVE",
       (SELECT DECODE(NVL(KTARCPT.PKPR,1),1,(SELECT KTARCPT3.TATARIF FROM KTARCPT KTARCPT3 WHERE KTARCPT3.PKPR IS NULL),
                      KTARCPT.TATARIF) FROM KTARCPT 
        WHERE (KTARCPT.PKPR=KLOGEMT.PKPR  OR KTARCPT.PKPR IS NULL )
              AND (KTARCPT.TCTC=KCPCPT.TCTC)
              AND (KTARCPT.ROWID = (SELECT MAX(KTARCPT2.ROWID) FROM KTARCPT KTARCPT2 WHERE (KTARCPT.PKPR=KTARCPT2.PKPR OR KTARCPT2.PKPR IS NULL) AND ( KTARCPT2.TCTC=KTARCPT.TCTC) AND (KTARCPT2.TADATVIG <= KCPIND.CODP))
              )        
        ) "P.U"
        FROM KCPIND, KCPCPT, KCNTLOC, KCNT, KLOGEMT
        WHERE ( KCPIND.CNCT=KCNTLOC.CNCT AND KCPIND.CTCT=KCNTLOC.CTCT )
        AND (KCNTLOC.CNCT=KCNT.CNCT AND KCNTLOC.CTCT=KCNT.CTCT AND KCNTLOC.LKLO=KCNT.LKLO)
        AND ( KCNT.LKLO=KLOGEMT.LKLO AND KCNT.SCSO=KLOGEMT.SCSO )
        AND ( KCPIND.CPIDF=KCPCPT.CPIDF AND KCPIND.SCSO=KCPCPT.SCSO)
        AND ( KCPCPT.LKLO = KCNTLOC.LKLO)
        AND (KLOGEMT.SCSO='PU')
        AND (KLOGEMT.ATC IS NOT NULL)
        AND (KCPIND.CODP >= '&&param1') 
        ORDER BY 1,4,6, KCPIND.CODP DESC
Ce que je souhaiterais obtenir c'est un résultat intégrant non pas le relevé, mais la consomation , c'est à dire la différence entre :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
("INDEX RELEVE" pour ROWNUM = 1 ) - ( "INDEX RELEVE" pour ROWNUM = 2 )
SI "MODULE"="MODULE" ET "CONTRAT"="CONTRAT" ET "NUM CPT"="NUM CPT"
J'ai bien essayé en passant par une réquête affreuse en jouant sur les dates et le ROWNUM du genre :
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
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
SELECT "NUMLIGNE",
       "MODULE",
       "ENTREE",
       "SORTIE",
       "CONTRAT",
       "DATE RELEVE",
       (SELECT "DATA2"."INDEX RELEVE" 
        FROM (SELECT ROWNUM "NUMLIGNE", KCPCPT.LKLO "MODULE",
                     KCPIND.CNCT "CONTRAT",
             KCNT.CNDCT "LOCATAIRE",
             KCPCPT.CPIDF "NUM CPT", 
             DECODE(KCPCPT.TCTC,'F','F FROID','C','C CHAUD') "TYPE CPT",
             DECODE(KCPIND.COACTIF,0,'HISTORIEE',1,'A TRAITER',KCPIND.COACTIF) "ETAT CONSO",
             KCPIND.CODP "DATE RELEVE",
             DECODE(NVL(KCPIND.CONM,0),0,KCPIND.CONI,KCPIND.CONM) "INDEX RELEVE",
             (SELECT DECODE(NVL(KTARCPT.PKPR,1),1,(SELECT KTARCPT3.TATARIF FROM KTARCPT KTARCPT3 WHERE KTARCPT3.PKPR IS NULL),
                            KTARCPT.TATARIF) FROM KTARCPT 
              WHERE (KTARCPT.PKPR=KLOGEMT.PKPR  OR KTARCPT.PKPR IS NULL)
                    AND (KTARCPT.TCTC=KCPCPT.TCTC)
                    AND (KTARCPT.ROWID = (SELECT MAX(KTARCPT2.ROWID) FROM KTARCPT KTARCPT2 WHERE (KTARCPT.PKPR=KTARCPT2.PKPR OR KTARCPT2.PKPR IS NULL) AND ( KTARCPT2.TCTC=KTARCPT.TCTC))
                    )        
              ) "P.U",
              DECODE (NVL(KCPIND.CONM,0),0,KCPIND.COCONSO,'')"CONSO"
              FROM KCPIND, KCPCPT, KCNTLOC, KCNT, KLOGEMT
              WHERE ( KCPIND.CNCT=KCNTLOC.CNCT AND KCPIND.CTCT=KCNTLOC.CTCT )
              AND (KCNTLOC.CNCT=KCNT.CNCT AND KCNTLOC.CTCT=KCNT.CTCT AND KCNTLOC.LKLO=KCNT.LKLO)
              AND ( KCNT.LKLO=KLOGEMT.LKLO AND KCNT.SCSO=KLOGEMT.SCSO )
              AND ( KCPIND.CPIDF=KCPCPT.CPIDF AND KCPIND.SCSO=KCPCPT.SCSO)
              AND ( KCPCPT.LKLO = KCNTLOC.LKLO)
              AND (KLOGEMT.SCSO='PU')
              AND (KLOGEMT.ATC IS NOT NULL)
              AND (KCPIND.CODP >= '01/07/2005') 
              ORDER BY 1,2,5, 7, KCPIND.CODP DESC ) "DATA2"
        WHERE ("DATA"."MODULE"="DATA2"."MODULE" AND "DATA"."CONTRAT"="DATA2"."CONTRAT")
          AND (("DATA"."NUMLIGNE"+ 1) ="DATA2"."NUMLIGNE") )  
          ||' - ' || 
          (SELECT "DATA3"."INDEX RELEVE" 
        FROM (SELECT ROWNUM "NUMLIGNE", KCPCPT.LKLO "MODULE",
                     KCPIND.CNCT "CONTRAT",
             KCNT.CNDCT "LOCATAIRE",
             KCPCPT.CPIDF "NUM CPT", 
             DECODE(KCPCPT.TCTC,'F','F FROID','C','C CHAUD') "TYPE CPT",
             DECODE(KCPIND.COACTIF,0,'HISTORIEE',1,'A TRAITER',KCPIND.COACTIF) "ETAT CONSO",
             KCPIND.CODP "DATE RELEVE",
             DECODE(NVL(KCPIND.CONM,0),0,KCPIND.CONI,KCPIND.CONM) "INDEX RELEVE",
             (SELECT DECODE(NVL(KTARCPT.PKPR,1),1,(SELECT KTARCPT3.TATARIF FROM KTARCPT KTARCPT3 WHERE KTARCPT3.PKPR IS NULL),
                            KTARCPT.TATARIF) FROM KTARCPT 
              WHERE (KTARCPT.PKPR=KLOGEMT.PKPR  OR KTARCPT.PKPR IS NULL)
                    AND (KTARCPT.TCTC=KCPCPT.TCTC)
                    AND (KTARCPT.ROWID = (SELECT MAX(KTARCPT2.ROWID) FROM KTARCPT KTARCPT2 WHERE (KTARCPT.PKPR=KTARCPT2.PKPR OR KTARCPT2.PKPR IS NULL) AND ( KTARCPT2.TCTC=KTARCPT.TCTC))
                    )        
              ) "P.U",
              DECODE (NVL(KCPIND.CONM,0),0,KCPIND.COCONSO,'')"CONSO"
              FROM KCPIND, KCPCPT, KCNTLOC, KCNT, KLOGEMT
              WHERE ( KCPIND.CNCT=KCNTLOC.CNCT AND KCPIND.CTCT=KCNTLOC.CTCT )
              AND (KCNTLOC.CNCT=KCNT.CNCT AND KCNTLOC.CTCT=KCNT.CTCT AND KCNTLOC.LKLO=KCNT.LKLO)
              AND ( KCNT.LKLO=KLOGEMT.LKLO AND KCNT.SCSO=KLOGEMT.SCSO )
              AND ( KCPIND.CPIDF=KCPCPT.CPIDF AND KCPIND.SCSO=KCPCPT.SCSO)
              AND ( KCPCPT.LKLO = KCNTLOC.LKLO)
              AND (KLOGEMT.SCSO='PU')
              AND (KLOGEMT.ATC IS NOT NULL)
              AND (KCPIND.CODP >= '01/07/2005') 
              ORDER BY 1,2,5, 7, KCPIND.CODP DESC ) "DATA3"
        WHERE ("DATA"."MODULE"="DATA3"."MODULE" AND "DATA"."CONTRAT"="DATA3"."CONTRAT")
          AND ("DATA"."NUMLIGNE"="DATA3"."NUMLIGNE") ) "CONSO"
FROM (SELECT ROWNUM "NUMLIGNE", KCPCPT.LKLO "MODULE",
       TO_CHAR(KCNTLOC.CDECL,'DD-MM-YYYY')  "ENTREE",
       TO_CHAR(KCNTLOC.CDSCL,'DD-MM-YYYY') "SORTIE",
       KCPIND.CNCT "CONTRAT",
       KCNT.CNDCT "LOCATAIRE",
       KCPCPT.CPIDF "NUM CPT", 
       DECODE(KCPCPT.TCTC,'F','F FROID','C','C CHAUD') "TYPE CPT",
       DECODE(KCPIND.COACTIF,0,'HISTORIEE',1,'A TRAITER',KCPIND.COACTIF) "ETAT CONSO",
       KCPIND.CODP "DATE RELEVE",
       DECODE(NVL(KCPIND.CONM,0),0,KCPIND.CONI,KCPIND.CONM) "INDEX RELEVE",
       (SELECT DECODE(NVL(KTARCPT.PKPR,1),1,(SELECT KTARCPT3.TATARIF FROM KTARCPT KTARCPT3 WHERE KTARCPT3.PKPR IS NULL),
                      KTARCPT.TATARIF) FROM KTARCPT 
        WHERE (KTARCPT.PKPR=KLOGEMT.PKPR  OR KTARCPT.PKPR IS NULL)
              AND (KTARCPT.TCTC=KCPCPT.TCTC)
              AND (KTARCPT.ROWID = (SELECT MAX(KTARCPT2.ROWID) FROM KTARCPT KTARCPT2 WHERE (KTARCPT.PKPR=KTARCPT2.PKPR OR KTARCPT2.PKPR IS NULL) AND ( KTARCPT2.TCTC=KTARCPT.TCTC))
              )        
        ) "P.U",
        DECODE (NVL(KCPIND.CONM,0),0,KCPIND.COCONSO,'')"CONSO"
        FROM KCPIND, KCPCPT, KCNTLOC, KCNT, KLOGEMT
        WHERE ( KCPIND.CNCT=KCNTLOC.CNCT AND KCPIND.CTCT=KCNTLOC.CTCT )
        AND (KCNTLOC.CNCT=KCNT.CNCT AND KCNTLOC.CTCT=KCNT.CTCT AND KCNTLOC.LKLO=KCNT.LKLO)
        AND ( KCNT.LKLO=KLOGEMT.LKLO AND KCNT.SCSO=KLOGEMT.SCSO )
        AND ( KCPIND.CPIDF=KCPCPT.CPIDF AND KCPIND.SCSO=KCPCPT.SCSO)
        AND ( KCPCPT.LKLO = KCNTLOC.LKLO)
        AND (KLOGEMT.SCSO='PU')
        AND (KLOGEMT.ATC IS NOT NULL)
        AND (KCPIND.CODP >= '01/07/2005') 
        ORDER BY 1,2,5, 7, KCPIND.CODP DESC ) "DATA"
WHERE "DATA"."DATE RELEVE" >= TO_DATE('2005-11-01','YYYY-MM-DD')
Mais en plus d'être affreuse, cette requête ne me raméne pas le bon résultat...

Bon , vu la spécificité de ma demande, je n'en voudrais à personne de ne pas y répondre , en revanche , je remercie d'avance toutes personnes qui aura eut le courage de s'y pencher !

Merci donc et @+