Problème requête SQLServer

**fweds** · 20/08/2012, 19h09

Bonjour,

Je me casse la tête sur une requête SQLServer qui me renvoie une erreur OLE DB.

Voici la requête :

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
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
SELECT 
	ARTICLES.ART_CODE AS ART_CODE,	
	ARTICLES.ART_REF AS ART_REF,	
	ARTICLES.ART_CBAR AS ART_CBAR,	
	ARTICLES.ART_TYPE AS ART_TYPE,	
	ARTICLES.ART_TLOT AS ART_TLOT,	
	ARTICLES.ART_TGAMME AS ART_TGAMME,	
	ARTICLES.ART_CATEG AS ART_CATEG,	
	ARTICLES.FAR_CODE AS FAR_CODE,	
	ARTICLES.SFA_CODE AS SFA_CODE,	
	ARTICLES.ART_FARNAT AS ART_FARNAT,	
	ARTICLES.ART_SFANAT AS ART_SFANAT,	
	ARTICLES.ART_LIB AS ART_LIB,	
	ARTICLES.ART_LIBC AS ART_LIBC,	
	ARTICLES.ART_REMPL AS ART_REMPL,	
	ARTICLES.ART_MFACT AS ART_MFACT,	
	ARTICLES.ART_MACHAT AS ART_MACHAT,	
	ARTICLES.ART_UC_ACH AS ART_UC_ACH,	
	ARTICLES.ART_CD_ACH AS ART_CD_ACH,	
	ARTICLES.ART_UB_ACH AS ART_UB_ACH,	
	ARTICLES.ART_UC_STK AS ART_UC_STK,	
	ARTICLES.ART_CD_STK AS ART_CD_STK,	
	ARTICLES.ART_UB_STK AS ART_UB_STK,	
	ARTICLES.ART_UC_VTE AS ART_UC_VTE,	
	ARTICLES.ART_CD_VTE AS ART_CD_VTE,	
	ARTICLES.ART_UB_VTE AS ART_UB_VTE,	
	ARTICLES.ART_R_UAUV AS ART_R_UAUV,	
	ARTICLES.ART_R_USUV AS ART_R_USUV,	
	ARTICLES.ART_PRIXAU AS ART_PRIXAU,	
	ARTICLES.ART_QTEDFT AS ART_QTEDFT,	
	ARTICLES.ART_POIDSN AS ART_POIDSN,	
	ARTICLES.ART_POIDSB AS ART_POIDSB,	
	ARTICLES.ART_POIDST AS ART_POIDST,	
	ARTICLES.ART_NCOLIS AS ART_NCOLIS,	
	ARTICLES.ART_LONG AS ART_LONG,	
	ARTICLES.ART_LARG AS ART_LARG,	
	ARTICLES.ART_HAUT AS ART_HAUT,	
	ARTICLES.ART_VOLUME AS ART_VOLUME,	
	ARTICLES.ART_NII AS ART_NII,	
	ARTICLES.ART_DELAI AS ART_DELAI,	
	ARTICLES.ART_STOCK AS ART_STOCK,	
	ARTICLES.ART_DELCOM AS ART_DELCOM,	
	ARTICLES.ART_FREAP AS ART_FREAP,	
	ARTICLES.ART_NIMP AS ART_NIMP,	
	ARTICLES.ART_NSTAT AS ART_NSTAT,	
	ARTICLES.ART_NCOM AS ART_NCOM,	
	ARTICLES.ART_PERIMA AS ART_PERIMA,	
	ARTICLES.ART_DORT AS ART_DORT,	
	ARTICLES.ART_M_PRV AS ART_M_PRV,	
	ARTICLES.ART_I_PRV AS ART_I_PRV,	
	ARTICLES.ART_D_PRV AS ART_D_PRV,	
	ARTICLES.ART_S_PRV AS ART_S_PRV,	
	ARTICLES.ART_P_PRV AS ART_P_PRV,	
	ARTICLES.ART_T_APP AS ART_T_APP,	
	ARTICLES.ART_T_APP2 AS ART_T_APP2,	
	ARTICLES.ART_T_APP3 AS ART_T_APP3,	
	ARTICLES.ART_F_APP AS ART_F_APP,	
	ARTICLES.ART_F_APP2 AS ART_F_APP2,	
	ARTICLES.ART_F_APP3 AS ART_F_APP3,	
	ARTICLES.ART_T_ARR AS ART_T_ARR,	
	ARTICLES.ART_V_ARR AS ART_V_ARR,	
	ARTICLES.ART_PACHUB AS ART_PACHUB,	
	ARTICLES.ART_P_ACH AS ART_P_ACH,	
	ARTICLES.ART_P_COEF AS ART_P_COEF,	
	ARTICLES.ART_PVTEUB AS ART_PVTEUB,	
	ARTICLES.ART_TRENDU AS ART_TRENDU,	
	ARTICLES.ART_FRENDU AS ART_FRENDU,	
	ARTICLES.ART_P_VTE AS ART_P_VTE,	
	ARTICLES.ART_P_EURO AS ART_P_EURO,	
	ARTICLES.ART_DOUANE AS ART_DOUANE,	
	ARTICLES.ART_CONSIG AS ART_CONSIG,	
	ARTICLES.GAR_CODE AS GAR_CODE,	
	ARTICLES.FA1_CODE AS FA1_CODE,	
	ARTICLES.FA2_CODE AS FA2_CODE,	
	ARTICLES.FA3_CODE AS FA3_CODE,	
	ARTICLES.FA4_CODE AS FA4_CODE,	
	ARTICLES.FA5_CODE AS FA5_CODE,	
	ARTICLES.PCF_CODE AS PCF_CODE,	
	ARTICLES.ART_GROUPE AS ART_GROUPE,	
	ARTICLES.ART_DTCREE AS ART_DTCREE,	
	ARTICLES.ART_MEMO AS ART_MEMO,	
	ARTICLES.ART_IMAGE AS ART_IMAGE,	
	ARTICLES.ART_DTMAJ AS ART_DTMAJ,	
	ARTICLES.ART_USRMAJ AS ART_USRMAJ,	
	ARTICLES.ART_NUMMAJ AS ART_NUMMAJ,	
	ARTICLES.XXX_ZONE AS XXX_ZONE,	
	ARTICLES.XXX_ZONE2 AS XXX_ZONE2,	
	ARTICLES.XXX_ZONE3 AS XXX_ZONE3,	
	ARTICLES.XXX_ZONE4 AS XXX_ZONE4,	
	ARTICLES.ART_CONTRM AS ART_CONTRM,	
	ARTICLES.ART_MSUPPTPF1 AS ART_MSUPPTPF1,	
	ARTICLES.ART_MSUPPTPF2 AS ART_MSUPPTPF2,	
	ARTICLES.ART_REMMAX AS ART_REMMAX,	
	ARTICLES.ART_ASSEMBLAGE AS ART_ASSEMBLAGE,	
	ARTICLES.ART_PV_MAN AS ART_PV_MAN,	
	ARTICLES.ART_T_ACH AS ART_T_ACH,	
	ARTICLES.ART_F_ACH AS ART_F_ACH,	
	ARTICLES.ART_T_ACH2 AS ART_T_ACH2,	
	ARTICLES.ART_F_ACH2 AS ART_F_ACH2,	
	ARTICLES.ART_T_ACH3 AS ART_T_ACH3,	
	ARTICLES.ART_F_ACH3 AS ART_F_ACH3,	
	ART_STOCK.STK_REEL AS STK_REEL,	
	 CASE WHEN ART_PRIX.ART_DT_NEW > ARTICLES.ART_DTMAJ THEN ART_PRIX.ART_N_VTE ELSE ARTICLES.ART_P_VTE END AS ART_P_VTEB
FROM 
	ART_PRIX INNER JOIN ARTICLES ON ARTICLES.ART_CODE	=	ART_PRIX.ART_CODE,	
	ART_STOCK RIGHT OUTER JOIN ARTICLES ON ARTICLES.ART_CODE	=	ART_STOCK.ART_CODE
WHERE 
	(
	ARTICLES.ART_DTMAJ >= {Param_Date}
	AND	ART_STOCK.DEP_CODE = '001'
	AND	ART_PRIX.DEV_CODE = 'EUR'
)

L'erreur renvoyée est :

The object "%s" and "%s" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

J'ai tenté d'exécuter la requête sans correction, même problème

En fait je voudrais que la requête me renvoie aussi les enregistrements du fichier ARTICLES qui n'ont pas d'enregistrement dans le fichier ART_STOCK

Un peu d'aide svp serait bienvenue car je sèche

Merci d'avance

**Hibernatus34** · 21/08/2012, 08h27

Bonjour,

La requête a 2 tables ARTICLES dans le FROM.
Le FROM et le WHERE devraient ressembler à ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
FROM
    ARTICLES
    INNER JOIN ART_PRIX ON ART_PRIX.ART_CODE = ARTICLES.ART_CODE AND ART_PRIX.DEV_CODE = 'EUR'
    INNER JOIN ART_STOCK ON ART_STOCK.ART_CODE = ARTICLES.ART_CODE AND ART_STOCK.DEP_CODE = '001'
WHERE
    ARTICLES.ART_DTMAJ >= {Param_Date}

Personnellement je n'utilise que du SQL avec l'option hRequêteSansCorrection, c'est plus rapide et plus puissant.

**hpascal** · 21/08/2012, 09h04

Bonjour

Dans la requête d'Hibernatus34 je ferais plutôt LEFT JOIN ART_STOCK pour avoir aussi les articles sans stock

**Hibernatus34** · 21/08/2012, 10h03

J'ai transformé le RIGHT en INNER parce qu'il avait une condition sur un élément du stock : ART_STOCK.DEP_CODE = '001'
Rendant son RIGHT JOIN équivalent à un INNER JOIN.

Mais difficile de savoir ce qu'il voulait vraiment.

**fweds** · 21/08/2012, 10h11

Bonjour,

merci de m'avoir répondu aussi vite

Je suis en train de tester en conditions réelles la requête que j'ai modifié selon Hibernatus avec la modif de Pascal. Plus d'erreurs en mode test, je contrôle en fonctionnement réelle et je vous tiens informé.

Merci

**fweds** · 21/08/2012, 10h24

Bon ben ça fonctionne à merveille !!!!

Je vous remercie les gars, vous m'avez vraiment sauvé

Problème requête SQLServer [WD16]

WinDev

Vue hybride

Discussions similaires

Partager

Partager