Requête dans une autre requête SQL

Version imprimable

Bonjour et merci d'avance pour me dépanner !
Voilà j'ai une requête qui tire des information d'une autre requête... mais je ne sais pas comment faire pour en avoir qu'une seule !

Code:

1
2
3
4
5
6
7
8
9
SELECT F31122Z1.*, F31122Z1.SZOPSQ AS SéqFROM proddta.F31122Z1 TABLE1
WHERE (((F31122Z1.SZOPSQ)>50000 And (F31122Z1.SZOPSQ)<90000)) 
 
-- j'ai une sous requête ci-dessus que j'aimerais relier à ma requête finale ci-dessous. Comment faire ?
 
SELECT [TABLE1].SZEDDT, F4801.WADOCO, F4801.WAAITM, F4801.WADL01, Max([TABLE1].[SZOPSQ]) AS Séq
FROM [TABLE1] LEFT JOIN F4801 ON [TABLE1].SZDOCO = F4801.WADOCO
GROUP BY [TABLE1].SZEDDT, F4801.WADOCO, F4801.WAAITM, F4801.WADL01
ORDER BY Max([TABLE1].[SZOPSQ]);

Grand merci d'avance !
Chree74

16/07/2019, 08h54
Scriuiw

Quel est le lien entre les deux requêtes ?
Quels sont les champs qui permettraient de les joindre "SQLment" parlant ?
S'il n'y en a pas, produit cartésien ??

en faite je voudrais que le RIGHT JOIN du "SQL définitif" soit la "Sous requête" équivalente à ça :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Sous requête
FROM proddta.F31122Z1 
SELECT F3112.WLDOCO, Max(F3112.WLOPSQ) AS MaxDeWLOPSQ
FROM proddta.F3112
GROUP BY F3112.WLDOCO
HAVING (((F3112.WLDOCO)=473650))
 
 
-- SQL définitif
SELECT F3112.WLDOCO, F3112.WLKITA, Max(F3112.WLOPSQ) AS MaxDeWLOPSQ, F31122Z1.SZAN8, to_char ( to_date ( F31122Z1.SZEDDT +1900000 , 'YYYYDDD' )) DateDuMvt, (F31122Z1.szsoqs)/1000  Qté
FROM proddta.F31122Z1 
RIGHT JOIN proddta.F3112 ON (F31122Z1.SZOPSQ = F3112.WLOPSQ) AND (F31122Z1.SZDOCO = F3112.WLDOCO)
GROUP BY F3112.WLDOCO, F3112.WLKITA, F31122Z1.SZAN8, F31122Z1.SZEDDT, F31122Z1.sztyr, F31122Z1.szsoqs
HAVING (((Max(F3112.WLOPSQ))>40000 And (Max(F3112.WLOPSQ))<50000)) and F31122Z1.SZEDDT >= 119197 and F3112.WLDOCO = 473650 and F31122Z1.sztyr ='4' --And (Sum((F31122Z1.szsoqs))/1000)<>0 
;

est-ce possible de faire ?

Code:

1
2
3
4
5
...FROM proddta.F31122Z1
RIGHT JOIN SELECT SELECT F3112.WLDOCO, Max(F3112.WLOPSQ) AS MaxDeWLOPSQ
FROM proddta.F3112
GROUP BY F3112.WLDOCO
HAVING (((F3112.WLDOCO)=473650))...

Quelque chose comme ceci ?

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
-- SQL définitif
SELECT F3112.WLDOCO, F3112.WLKITA, Max(F3112.WLOPSQ) AS MaxDeWLOPSQ, F31122Z1.SZAN8, to_char ( to_date ( F31122Z1.SZEDDT +1900000 , 'YYYYDDD' )) DateDuMvt, (F31122Z1.szsoqs)/1000  Qté
FROM proddta.F31122Z1 
RIGHT JOIN 
  -- Sous requête
  (SELECT F3112.WLDOCO, Max(F3112.WLOPSQ) AS MaxDeWLOPSQ
  FROM proddta.F3112
  GROUP BY F3112.WLDOCO
  HAVING (((F3112.WLDOCO)=473650))
  )
  ON (F31122Z1.SZOPSQ = F3112.WLOPSQ) AND (F31122Z1.SZDOCO = F3112.WLDOCO)
GROUP BY F3112.WLDOCO, F3112.WLKITA, F31122Z1.SZAN8, F31122Z1.SZEDDT, F31122Z1.sztyr, F31122Z1.szsoqs
HAVING (((Max(F3112.WLOPSQ))>40000 And (Max(F3112.WLOPSQ))<50000)) and F31122Z1.SZEDDT >= 119197 and F3112.WLDOCO = 473650 and F31122Z1.sztyr ='4' --And (Sum((F31122Z1.szsoqs))/1000)<>0 
;

Variante avec clause WITH .. AS

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
-- Sous requête
WITH PDta AS
  (SELECT F3112.WLDOCO, Max(F3112.WLOPSQ) AS MaxDeWLOPSQ
  FROM proddta.F3112
  GROUP BY F3112.WLDOCO
  HAVING (((F3112.WLDOCO)=473650))
  )
SELECT F3112.WLDOCO, F3112.WLKITA, Max(F3112.WLOPSQ) AS MaxDeWLOPSQ, F31122Z1.SZAN8, to_char ( to_date ( F31122Z1.SZEDDT +1900000 , 'YYYYDDD' )) DateDuMvt, (F31122Z1.szsoqs)/1000  Qté
FROM proddta.F31122Z1 
RIGHT JOIN PDta
  ON (F31122Z1.SZOPSQ = PDta.WLOPSQ) AND (F31122Z1.SZDOCO = PDta.WLDOCO)
GROUP BY F3112.WLDOCO, F3112.WLKITA, F31122Z1.SZAN8, F31122Z1.SZEDDT, F31122Z1.sztyr, F31122Z1.szsoqs
HAVING (((Max(F3112.WLOPSQ))>40000 And (Max(F3112.WLOPSQ))<50000)) and F31122Z1.SZEDDT >= 119197 and F3112.WLDOCO = 473650 and F31122Z1.sztyr ='4' --And (Sum((F31122Z1.szsoqs))/1000)<>0 
;

Merci Scriuiw pour cette première approche !

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 1ère variante
SELECT F3112.WLDOCO, F3112.WLKITA, Max(F3112.WLOPSQ) AS MaxDeWLOPSQ, F31122Z1.SZAN8, to_char ( to_date ( F31122Z1.SZEDDT +1900000 , 'YYYYDDD' )) DateDuMvt, (F31122Z1.szsoqs)/1000  Qté
FROM proddta.F31122Z1 
RIGHT JOIN 
  -- Sous requête
  (SELECT F3112.WLDOCO, Max(F3112.WLOPSQ) AS MaxDeWLOPSQ
  FROM proddta.F3112
  GROUP BY F3112.WLDOCO
  HAVING (((F3112.WLDOCO)=473650))
  )
  ON (F31122Z1.SZOPSQ = F3112.WLOPSQ) AND (F31122Z1.SZDOCO = F3112.WLDOCO)
GROUP BY F3112.WLDOCO, F3112.WLKITA, F31122Z1.SZAN8, F31122Z1.SZEDDT, F31122Z1.sztyr, F31122Z1.szsoqs
HAVING (((Max(F3112.WLOPSQ))>40000 And (Max(F3112.WLOPSQ))<50000)) and F31122Z1.SZEDDT >= 119197 and F3112.WLDOCO = 473650 and F31122Z1.sztyr ='4' --And (Sum((F31122Z1.szsoqs))/1000)<>0 
;

Voici le message renvoyé :

Citation:

ORA-00904: "F3112"."WLDOCO" : identificateur non valide
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Erreur à la ligne 11, colonne 63

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 2ème variante
WITH PDta AS
  (SELECT F3112.WLDOCO, Max(F3112.WLOPSQ) AS MaxDeWLOPSQ
  FROM proddta.F3112
  GROUP BY F3112.WLDOCO
  HAVING (((F3112.WLDOCO)=473650))
  )
SELECT F3112.WLDOCO, F3112.WLKITA, Max(F3112.WLOPSQ) AS MaxDeWLOPSQ, F31122Z1.SZAN8, to_char ( to_date ( F31122Z1.SZEDDT +1900000 , 'YYYYDDD' )) DateDuMvt, (F31122Z1.szsoqs)/1000  Qté
FROM proddta.F31122Z1 
RIGHT JOIN PDta ON (F31122Z1.SZOPSQ = PDta.WLOPSQ) AND (F31122Z1.SZDOCO = PDta.WLDOCO)
GROUP BY F3112.WLDOCO, F3112.WLKITA, F31122Z1.SZAN8, F31122Z1.SZEDDT, F31122Z1.sztyr, F31122Z1.szsoqs
HAVING (((Max(F3112.WLOPSQ))>40000 And (Max(F3112.WLOPSQ))<50000)) and F31122Z1.SZEDDT >= 119197 and F3112.WLDOCO = 473650 and F31122Z1.sztyr ='4' --And (Sum((F31122Z1.szsoqs))/1000)<>0 
;

Voici le message renvoyé :

Citation:

ORA-00904: "PDTA"."WLOPSQ" : identificateur non valide
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Erreur à la ligne 10, colonne 40

Alors je n'ai fait qu'un assemblage de vos requêtes pour vous montrer les possibilités par sous-requête et par la clause WITH, par contre je n'ai absolument pas vérifier la cohérence de vos champs et des jointures
:oops::oops::oops:

Le problème vient, je pense, des préfixes de table qui ne passent pas bien lorsque l'on fait ce type de requête.
Essayez ceci

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
-- 1ère variante
SELECT F3112.WLDOCO, F3112.WLKITA, Max(F3112.WLOPSQ) AS MaxDeWLOPSQ, F31122Z1.SZAN8, to_char ( to_date ( F31122Z1.SZEDDT +1900000 , 'YYYYDDD' )) DateDuMvt, (F31122Z1.szsoqs)/1000  Qté
FROM proddta.F31122Z1 
RIGHT JOIN 
  -- Sous requête
  (SELECT WLDOCO, Max(WLOPSQ) AS MaxDeWLOPSQ
  FROM proddta.F3112
  GROUP BY WLDOCO
  HAVING (WLDOCO=473650)
  ) F3112
  ON (F31122Z1.SZOPSQ = F3112.WLOPSQ) AND (F31122Z1.SZDOCO = F3112.WLDOCO)
GROUP BY F3112.WLDOCO, F3112.WLKITA, F31122Z1.SZAN8, F31122Z1.SZEDDT, F31122Z1.sztyr, F31122Z1.szsoqs
HAVING (((Max(F3112.WLOPSQ))>40000 And (Max(F3112.WLOPSQ))<50000)) and F31122Z1.SZEDDT >= 119197 and F3112.WLDOCO = 473650 and F31122Z1.sztyr ='4' --And (Sum((F31122Z1.szsoqs))/1000)<>0 
;

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
 
WITH PDta AS
  (SELECT WLDOCO, Max(WLOPSQ) AS MaxDeWLOPSQ
  FROM proddta.F3112
  GROUP BY WLDOCO
  HAVING (WLDOCO=473650)
  )
SELECT F3112.WLDOCO, F3112.WLKITA, Max(F3112.WLOPSQ) AS MaxDeWLOPSQ, F31122Z1.SZAN8, to_char ( to_date ( F31122Z1.SZEDDT +1900000 , 'YYYYDDD' )) DateDuMvt, (F31122Z1.szsoqs)/1000  Qté
FROM proddta.F31122Z1 
RIGHT JOIN PDta ON (F31122Z1.SZOPSQ = PDta.WLOPSQ) AND (F31122Z1.SZDOCO = PDta.WLDOCO)
GROUP BY F3112.WLDOCO, F3112.WLKITA, F31122Z1.SZAN8, F31122Z1.SZEDDT, F31122Z1.sztyr, F31122Z1.szsoqs
HAVING (((Max(F3112.WLOPSQ))>40000 And (Max(F3112.WLOPSQ))<50000)) and F31122Z1.SZEDDT >= 119197 and F3112.WLDOCO = 473650 and F31122Z1.sztyr ='4' --And (Sum((F31122Z1.szsoqs))/1000)<>0 
;

même anomalie ! Pour info : les 2 SQLs pris séparément fonctionnent.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 1ère variante v2
SELECT F3112.WLDOCO, F3112.WLKITA, Max(F3112.WLOPSQ) AS MaxDeWLOPSQ, F31122Z1.SZAN8, to_char ( to_date ( F31122Z1.SZEDDT +1900000 , 'YYYYDDD' )) DateDuMvt, (F31122Z1.szsoqs)/1000  Qté
FROM proddta.F31122Z1 
RIGHT JOIN 
  -- Sous requête
  (SELECT WLDOCO, Max(WLOPSQ) AS MaxDeWLOPSQ
  FROM proddta.F3112
  GROUP BY WLDOCO
  HAVING (WLDOCO=473650)
  ) F3112
  ON (F31122Z1.SZOPSQ = F3112.WLOPSQ) AND (F31122Z1.SZDOCO = F3112.WLDOCO)
GROUP BY F3112.WLDOCO, F3112.WLKITA, F31122Z1.SZAN8, F31122Z1.SZEDDT, F31122Z1.sztyr, F31122Z1.szsoqs
HAVING (((Max(F3112.WLOPSQ))>40000 And (Max(F3112.WLOPSQ))<50000)) and F31122Z1.SZEDDT >= 119197 and F3112.WLDOCO = 473650 and F31122Z1.sztyr ='4' --And (Sum((F31122Z1.szsoqs))/1000)<>0 
;

Citation:

RA-00904: "F3112"."WLOPSQ" : identificateur non valide
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Erreur à la ligne 11, colonne 26

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 2ème variante v2
WITH PDta AS
  (SELECT WLDOCO, Max(WLOPSQ) AS MaxDeWLOPSQ
  FROM proddta.F3112
  GROUP BY WLDOCO
  HAVING (WLDOCO=473650)
  )
SELECT F3112.WLDOCO, F3112.WLKITA, Max(F3112.WLOPSQ) AS MaxDeWLOPSQ, F31122Z1.SZAN8, to_char ( to_date ( F31122Z1.SZEDDT +1900000 , 'YYYYDDD' )) DateDuMvt, (F31122Z1.szsoqs)/1000  Qté
FROM proddta.F31122Z1 
RIGHT JOIN PDta ON (F31122Z1.SZOPSQ = PDta.WLOPSQ) AND (F31122Z1.SZDOCO = PDta.WLDOCO)
GROUP BY F3112.WLDOCO, F3112.WLKITA, F31122Z1.SZAN8, F31122Z1.SZEDDT, F31122Z1.sztyr, F31122Z1.szsoqs
HAVING (((Max(F3112.WLOPSQ))>40000 And (Max(F3112.WLOPSQ))<50000)) and F31122Z1.SZEDDT >= 119197 and F3112.WLDOCO = 473650 and F31122Z1.sztyr ='4' --And (Sum((F31122Z1.szsoqs))/1000)<>0 
;

Citation:

ORA-00904: "PDTA"."WLOPSQ" : identificateur non valide
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Erreur à la ligne 10, colonne 40

18/07/2019, 11h56
Waldar

Il faut corriger vos noms de colonne, c'est un problème syntaxique (vraiment) simple.

Voilà ce que je voulais

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT  F31122Z1.SZDOCO OFs,  F3112.WLKITA CodeArticle,PDta.IMDSC1 LibelleArticle , Max(F3112.WLOPSQ)  AS SEQ, F31122Z1.SZAN8 OPERATEUR, to_char ( to_date ( F31122Z1.SZEDDT +1900000 , 'YYYYDDD' ))  DateDuMvt,
sum((F31122Z1.szsoqs)/1000)  as  Qté
FROM proddta.F31122Z1
inner join proddta.f3112 on (szdoco = wldoco) and (szopsq = wlopsq) 
RIGHT JOIN   (
 
SELECT F3112.WLDOCO, F4101.IMDSC1, Max(F3112.WLOPSQ) AS MaxDeWLOPSQ
FROM proddta.F3112 LEFT JOIN proddta.F4101 ON (F3112.WLKITA = F4101.IMAITM)
GROUP BY F3112.WLDOCO, F4101.IMDSC1) PDta ON (F31122Z1.SZOPSQ = PDta.MaxDeWLOPSQ) AND (F31122Z1.SZDOCO = PDta.WLDOCO)
GROUP BY  F31122Z1.SZDOCO, F3112.WLKITA, F31122Z1.SZAN8, F31122Z1.SZEDDT, F31122Z1.sztyr, PDta.IMDSC1
HAVING (((Max(F3112.WLOPSQ))>10000 And (Max(F3112.WLOPSQ))<60000)) and (F31122Z1.SZEDDT = 119203) and (F31122Z1.sztyr ='4') 
ORDER BY (Max(F3112.WLOPSQ)) asc, F3112.WLKITA desc
;