Nom de table invalide aprés deux clauses WITH

**pytha** · 15/05/2014, 12h01

Bonjour,

Après avoir rajouter une deuxième clause With dans ma requête afin de pouvoir stocker un calcul, pour ensuite faire une comparaison dessus, j'obtiens un message d'erreur lorsque je l'enregistre :

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
WITH
 
FACPRI AS (SELECT 
 
SOCIETE, SITE, DATFAC, NUMFAC, DEVISE, BPCCODE, CLIENT, ITEM, DES1, DES2, DESBPC, PRIHT, NUMCOM, COMCLI, DATCOM, NUMLIV, DATEXP, QTY, UOM, PRIX1, PRIX2
 
FROM
 
(SELECT
 
SIH.CPY_0 AS SOCIETE,
SIH.FCY_0 AS SITE,
SIH.ACCDAT_0 AS DATFAC,
SIH.NUM_0 AS NUMFAC,
SIH.CUR_0 AS DEVISE,
SID.BPCINV_0 AS BPCCODE,
BPR.BPRNAM_0 AS CLIENT,
SID.ITMREF_0 AS ITEM,
SID.ITMDES_0 AS DES1,
SID.ITMDES1_0 AS DES2,
ITU.ITMREFBPC_0 AS DESBPC,
SID.GROPRI_0 AS PRIHT,
SID.SOHNUM_0 AS NUMCOM,
SOH.CUSORDREF_0 AS COMCLI,
SOQ.ORDDAT_0 AS DATCOM,
SID.SDHNUM_0 AS NUMLIV,
SDD.SHIDAT_0 AS DATEXP,
SDD.QTY_0 AS QTY,
SDD.SAU_0 AS UOM,
(SELECT MIN(SPL.PRI_0) FROM SPRICLIST SPL WHERE SIH.CPY_0 = SPL.PLICRI1_0 
AND SIH.CUR_0 = SPL.CUR_0 
AND SID.ITMREF_0 = SPL.PLICRI2_0 
AND SDD.BPCORD_0 = SPL.PLICRI3_0 
AND SDD.BPAADD_0 = SPL.PLICRI4_0 
AND SIH.ACCDAT_0 >= SPL.PLISTRDAT_0 
AND SIH.ACCDAT_0 <= SPL.PLIENDDAT_0 
AND SID.QTY_0 >= SPL.MINQTY_0 
AND SID.QTY_0 <= SPL.MAXQTY_0
AND SPL.PLI_0 = 'NIEF01') AS PRIX1,
(SELECT MIN(SPL1.PRI_0) FROM SPRICLIST SPL1 WHERE SIH.CPY_0 = SPL1.PLICRI1_0 
AND SIH.CUR_0 = SPL1.CUR_0 
AND SID.ITMREF_0 = SPL1.PLICRI2_0 
AND SDD.BPCORD_0 = SPL1.PLICRI3_0 
AND SIH.ACCDAT_0 >= SPL1.PLISTRDAT_0
AND SIH.ACCDAT_0 <= SPL1.PLIENDDAT_0
AND SID.QTY_0 >= SPL1.MINQTY_0
AND SID.QTY_0 <= SPL1.MAXQTY_0
AND SPL1.PLI_0 = 'NIEF02') AS PRIX2
 
FROM
 
SINVOICED SID
JOIN SINVOICE SIH ON SID.NUM_0 = SIH.NUM_0
JOIN BPARTNER BPR ON SID.BPCINV_0 = BPR.BPRNUM_0
LEFT OUTER JOIN SORDERQ SOQ ON SID.SOHNUM_0 = SOQ.SOHNUM_0 AND SID.SOPLIN_0 = SOQ.SOPLIN_0 AND SID.SOQSEQ_0 = SOQ.SOQSEQ_0
LEFT OUTER JOIN SORDER SOH ON SID.SOHNUM_0 = SOH.SOHNUM_0
LEFT OUTER JOIN ITMBPC ITU ON SOQ.BPCORD_0 = ITU.BPCNUM_0 AND SOQ.ITMREF_0 = ITU.ITMREF_0
LEFT OUTER JOIN SDELIVERYD SDD ON SID.SDHNUM_0 = SDD.SDHNUM_0 AND SID.SDDLIN_0 = SDD.SDDLIN_0
 
WHERE
 
SIH.CPY_0 BETWEEN %1% AND %2% AND
SIH.FCY_0 BETWEEN %3% AND %4% AND
SIH.ACCDAT_0 BETWEEN %5% AND %6%
 
)),
 
RESULT AS (
 
SELECT SOCIETE, SITE, DATFAC, NUMFAC, DEVISE, BPCCODE, CLIENT, ITEM, DES1, DES2, DESBPC, PRIHT, NUMCOM, COMCLI, DATCOM, NUMLIV, DATEXP, QTY, UOM, PRIX1, PRIX2,
CASE 
WHEN (PRIX1>0 AND PRIX2>0 AND PRIX1<PRIX2 AND PRIX1<>PRIHT) THEN ((PRIX1-PRIHT)*QTY)
WHEN (PRIX1>0 AND PRIX1 <> PRIHT) THEN ((PRIX1-PRIHT)*QTY)
WHEN (PRIX2>0 AND PRIX2 <> PRIHT) THEN ((PRIX2-PRIHT)*QTY)
ELSE 0
END AS MNTREGUL
 
FROM FACPRI),
 
SELECT SOCIETE, SITE, DATFAC, NUMFAC, DEVISE, BPCCODE, CLIENT, ITEM, DES1, DES2, DESBPC, PRIHT, NUMCOM, COMCLI, DATCOM, NUMLIV, DATEXP, QTY, UOM, PRIX1, PRIX2, MNTREGUL
FROM RESULT
WHERE MNTREGUL <> 0

Si j'utilise pas mon deuxième WITH, et que je passe par select ça marche. Mais après je ne sais comment mettre un critère sur le résultat de mon CASE.

Merci pour votre aide par avance,

pyth