bonjour a tous voila je doit faire une application en java sur un as400
qui me sortira le top purchase order.(top commande).

j'ai donc fait ma requette sql mais elle est trop gourmande si quelle qu'un a une idée pour la simplifier.

c'est une veille base de donner et il arrive que des table differente est les même nom de champs.

d'avance merci de votre aide

voici la requete.


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
select  tb2.g2noms, cg2.g2grou, b12.noart,  ap1.artlib, ap1.artnpk, 
ap1.artnca, sum(b12.qtyord) as total_pack, u1.arunum, b12.majvtf,
 
(select sum(b12b.qtyord)from roxusbas.bilf012 b12b, roxusbas.bilf009 b09b, roxusbas.artusp01 up1 
left outer join roxusbas.clig2p01 cg2b on b09b.codcli  = cg2b.g2clie 
 
where b12b.numbol = b09b.numbol and b09b.datsta = b12b.datsta and  b09b.datshi>= 20080401 
and up1.aruold = b12b.noart and b09b.datsta <= 20080416  and b09b.status<>9 
and  b09b.codsit = 06 and b12b.codsit = 06 and b12b.majvtf = 2
and up1.arunum = u1.arunum and cg2b.g2grou = cg2.g2grou
group by b12b.noart, cg2b.g2grou) as old_total_case,
 
(select sum(b12b.qtyord)from roxusbas.bilf012 b12b, roxusbas.bilf009 b09b, roxusbas.artusp01 up1
left outer join roxusbas.clig2p01 cg2b on b09b.codcli  = cg2b.g2clie 
where b12b.numbol = b09b.numbol and b09b.datsta = b12b.datsta and  b09b.datshi>= 20080401 
and b09b.datsta <= 20080416  and b09b.status<>9 and up1.aruold = b12b.noart 
and  b09b.codsit = 06 and b12b.codsit = 06 and b12b.majvtf = 1 and
and up1.arunum = u1.arunum and cg2b.g2grou = cg2.g2grou
group by b12b.noart, cg2b.g2grou) as old_total_palette,
 
(select sum(b12.qtyord) from roxusbas.bilf012 b12, roxusbas.bilf009 b09 
left outer join roxusbas.clig2p01 cg2b on b09.codcli  = cg2b.g2clie 
where b12.numbol = b09.numbol and b09.datsta = b12.datsta and  b09.datshi>= 20090401 
and b09.datshi<=20090416  and b09.status<>9 
and  b09.codsit = 06 and b12.codsit = 06  
 
and cg2b.g2grou = cg2.g2grou group by cg2b.g2grou ) as total_national_group 
 
from roxusbas.bilf009 b09, roxusbas.bilf012 b12  
left outer join roxusbas.clig2p01 cg2 on b09.codcli  = cg2.g2clie 
left outer join roxusbas.tabg2p01 tb2 on cg2.g2grou = tb2.g2ngro 
left outer join roxusbas.artusp01 u1 on u1.aruold = b12.noart 
left outer join roxusbas.artpfp01 ap1 on ap1.artnum = u1.arunum  
 
where b12.numbol = b09.numbol and b09.datsta = b12.datsta and  b09.datshi>= 20090401 
and b09.datshi<=20090416  and b09.status<>9 
 
and  b09.codsit = 06 and b12.codsit = 06
 
group by tb2.g2noms, cg2.g2grou, b12.noart,  ap1.artlib, 
ap1.artnpk, ap1.artnca , u1.arunum, b12.majvtf, 
 
 
(select sum(b12b.qtyord)from roxusbas.bilf012 b12b, roxusbas.bilf009 b09b, roxusbas.artusp01 up1
left outer join roxusbas.clig2p01 cg2b on b09b.codcli  = cg2b.g2clie 
where b12b.numbol = b09b.numbol and b09b.datsta = b12b.datsta and  b09b.datshi>= 20080401 
and b09b.datsta <= 20080416  and b09b.status<>9 and up1.aruold = b12b.noart 
and  b09b.codsit = 06 and b12b.codsit = 06 and b12b.majvtf = 2 and
and up1.arunum = u1.arunum and cg2b.g2grou = cg2.g2grou
group by b12b.noart, cg2b.g2grou),
 
(select sum(b12b.qtyord)from roxusbas.bilf012 b12b, roxusbas.bilf009 b09b, roxusbas.artusp01 up1
left outer join roxusbas.clig2p01 cg2b on b09b.codcli  = cg2b.g2clie 
where b12b.numbol = b09b.numbol and b09b.datsta = b12b.datsta and  b09b.datshi>= 20080401 
and b09b.datsta <= 20080416  and b09b.status<>9 and up1.aruold = b12b.noart 
and  b09b.codsit = 06 and b12b.codsit = 06 and b12b.majvtf = 1 and
and up1.arunum = u1.arunum and cg2b.g2grou = cg2.g2grou
group by b12b.noart, cg2b.g2grou),
 
(select sum(b12.qtyord) from roxusbas.bilf012 b12, roxusbas.bilf009 b09 
left outer join roxusbas.clig2p01 cg2b on b09.codcli  = cg2b.g2clie 
 
where b12.numbol = b09.numbol and b09.datsta = b12.datsta and  b09.datshi>= 20090401 
and b09.datshi<=20090416  and b09.status<>9  
 
and  b09.codsit = 06 and b12.codsit = 06 
 
and cg2b.g2grou = cg2.g2grou 
group by cg2b.g2grou ) 
 
order by total_national_group desc;