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
113
114
115
116
117
|
Select ite.ItemExtnumber , Ite.colRoot , Ite.wsroot , Ite.ItemExtCode , De_itm.MultiDescription , p.p_01 , s.s_Qty , Itm.ItemMainDesc2 , Itm.ItemMainType
From ItemExts as Ite
Left Outer Join ItemMains as itm ON (Ite.ItemExtMain = Itm.ItemMainNumber AND Ite.ItemExtMainCol = Itm.ColRoot AND Ite.ItemExtMainWs = Itm.WsRoot )
Left Outer Join Descriptions as de_itm ON ( Itm.ItemMainNumber = De_itm.MultiCode AND Itm.ColRoot = De_itm.MultiCodeCol AND Itm.WsRoot = De_itm.MultiCodeWs
and de_itm.MultiTable = 5 and de_Itm.multilang = 1 and de_Itm.Multilangcol = 0 and de_itm.Multilangws = 0 )
Left Outer Join Units as un ON (itm.itemmainunit = un.unitnumber AND itm.itemmainunitcol = un.colroot AND itm.itemmainunitws = un.wsroot)
Left Outer Join Descriptions as de_Un on (un.unitNumber = de_un.MultiCode AND un.ColRoot = de_un.MultiCodeCol AND un.WsRoot = de_un.MultiCodeWs
and de_un.multiTable = 24 and de_un.Multilang = 1 and de_un.multilangcol = 0 and de_un.multilangws = 0 )
Left Outer Join Seasons as se ON (itm.itemmainseason = se.seasonnumber AND itm.itemmainseasoncol = se.colroot AND itm.itemmainseasonws = se.wsroot)
Left Outer Join Descriptions as de_Se on (se.seasonNumber = de_se.MultiCode AND se.ColRoot = de_se.MultiCodeCol AND se.WsRoot = de_se.MultiCodeWs
and de_se.multiTable = 30 and de_se.Multilang = 1 and de_se.multilangcol = 0 and de_se.multilangws = 0 )
Left Outer Join brands as br on (itm.itemmainbrand = br.brandnumber and itm.itemmainbrandcol = br.colroot and itm.itemmainbrandws = br.wsroot)
Left Outer Join Descriptions as de_br on (br.brandnumber = de_br.multicode and br.colroot = de_br.multicodecol and br.wsroot = de_br.multicodews
and de_br.multitable = 82 and de_br.multilang = 1 and de_br.multilangcol = 0 and de_br.multilangws = 0 )
Left Outer Join Documents as do on ( Itm.itemMainLabel = do.documentnumber and itm.itemmainlabelcol = do.colroot and itm.itemmainLabelws = do.wsroot )
Left Outer Join Descriptions as de_do on (do.documentnumber = de_do.multicode and do.colroot = de_do.multiCodecol and do.wsroot = de_do.multicodews
and de_do.MultiTable = 102 and de_do.multilang = 1 and de_DO.multilangcol = 0 and de_do.multilangws = 0 )
Left Outer Join families as fa on ( itm.itemmainFamily = fa.familynumber and itm.itemmainfamilycol = fa.colroot and itm.itemmainfamilyws = fa.wsroot )
Left Outer Join familyDefs as fa_gr on ( fa.familyGroup = fa_gr.familydefnumber and fa.familyGroupCol = fa_gr.colroot and fa.familyGroupws = fa_gr.wsroot )
Left Outer Join Descriptions as de_fa_gr on ( fa_gr.familydefnumber = de_fa_gr.multicode and fa_gr.colroot = de_fa_gr.multicodecol and fa_gr.wsroot = de_fa_gr.multicodews
and de_fa_gr.multitable = 90 and de_fa_gr.multilang = 1 and de_fa_gr.multilangcol = 0 and de_fa_gr.multilangws = 0 )
Left Outer Join familyDefs as fa_sg on ( fa.familysGroup = fa_sg.familydefnumber and fa.familysGroupCol = fa_sg.colroot and fa.familysGroupws = fa_sg.wsroot )
Left Outer Join Descriptions as de_fa_sg on ( fa_sg.familydefnumber = de_fa_sg.multicode and fa_sg.colroot = de_fa_sg.multicodecol and fa_sg.wsroot = de_fa_sg.multicodews
and de_fa_sg.multitable = 90 and de_fa_sg.multilang = 1 and de_fa_sg.multilangcol = 0 and de_fa_sg.multilangws = 0 )
Left Outer Join familyDefs as fa_de on ( fa.familydepart = fa_de.familydefnumber and fa.familydepartCol = fa_de.colroot and fa.familydepartws = fa_de.wsroot )
Left Outer Join Descriptions as de_fa_de on ( fa_de.familydefnumber = de_fa_de.multicode and fa_de.colroot = de_fa_de.multicodecol and fa_de.wsroot = de_fa_de.multicodews
and de_fa_de.multitable = 90 and de_fa_de.multilang = 1 and de_fa_de.multilangcol = 0 and de_fa_de.multilangws = 0 )
Left Outer Join qtyDiscounts as qd on ( itm.itemmainQtyDisc = qd.qtyDiscountnumber and itm.itemMainQtyDisccol = qd.colroot and itm.itemmainqtydiscws = qd.wsroot )
Left Outer Join Descriptions as de_Qd on ( qd.qtydiscountnumber = de_qd.multicode and qd.colroot = de_qd.multicodecol and qd.wsroot = de_qd.multicodews
and de_qd.multitable = 95 and de_qd.multilang = 1 and de_qd.multilangcol = 0 and de_qd.multilangws = 0 )
LEFT OUTER JOIN (
Select sum(case T.TariffOrder when 1 then P.ItemPricePrice else 0 end ) as P_01 ,
sum(case T.TariffOrder when 2 then P.ItemPricePrice else 0 end ) as P_02 ,
sum(case T.TariffOrder when 3 then P.ItemPricePrice else 0 end ) as P_03 ,
sum(case T.TariffOrder when 4 then P.ItemPricePrice else 0 end ) as P_04 ,
sum(case T.TariffOrder when 5 then P.ItemPricePrice else 0 end ) as P_05 ,
P.ItemPriceItemExt as p_Id , P.ItemPriceItemextCol as p_Col , P.ItemPriceItemextws as p_Ws
FROM ItemPrices as P
Left Outer Join Tariffs as T on ( p.ItemPriceTariff = t.Tariffnumber and p.ItemPriceTariffCol = t.Colroot and p.ItemPriceTariffWs = t.wsroot )
Left Outer Join ProfilePrices as pp on ( t.TariffPrices = pp.priceNumber and t.TariffPricesCol = pp.colroot and t.TariffPricesWs = pp.wsroot )
Left Outer Join objectlist as ol on ( ol.objectcolprices = pp.PriceProfile and ol.objectcolpricescol = pp.PriceProfilecol and ol.objectcolpricesws = pp.PriceProfileWs )
where ol.objectcolnumber = 1
group by p.ItemPriceItemext , p.ItemPriceItemExtCol, p.ItemPriceItemExtWs
) as p on ( p_id = Ite.ItemExtnumber and p_col = Ite.colroot and p_ws = Ite.wsroot )
Left Outer Join ItemQties as itq on ( itq.itemQtyItemExt = ite.ItemExtnumber and itq.itemQtyItemExtcol = ite.colroot and itq.itemQtyItemExtws = ite.wsroot and itq.itemQtycollect = 1 )
Left Outer Join ItemStocks as its on ( Its.ItemStockItemQty = itq.itemQtyNumber and Its.ItemStockItemQtycol = itq.colroot and its.ItemStockItemQtyws = itq.wsroot )
Left Outer Join (
Select
( Case when s1_Qty is null then s2_Qty else s1_Qty end ) as s_Qty
, ( case when s1_Ordered is null then s2_Ordered else s1_ordered end ) as s_Ordered
, ( case when s1_Reserved is null then s2_reserved else s1_Reserved end ) as s_REserved
, ( case when s1_Consig is null then s2_Consig else s1_Consig end ) as s_Consig
, ( case when s1_Call is null then s2_Call else s1_Call end ) as s_Call
, ( case when s1_Ask is null then s2_Ask else s1_Ask end ) as s_Ask
, ( case when s1_Transfer is null then s2_Transfer else s1_Transfer end ) as s_Transfer
, ( case when s1_Label is null then s2_Label else s1_Label end ) as s_Label
, ( case when s2_Min is null then 0 else s2_Min end ) as s_min
, ( case when s2_Max is null then 0 else s2_Max end ) as s_Max
, ( case when s2_Place is null then '' else s2_Place end ) as s_Place
, ( case when s2_Volume is null then 0 else s2_volume end ) as s_volume
, ( case when s2_Brut is null then 0 else s2_Brut end ) as s_Brut
, ( case when s2_net is null then 0 else s2_Net end ) as s_net
, ( ( Case when s1_Qty is null then s2_Qty else s1_Qty end ) -
( case when s1_Reserved is null then s2_reserved else s1_Reserved end ) -
( case when s1_Consig is null then s2_Consig else s1_Consig end ) ) as s_QtyFree
, Itq.ItemQtyItemExt as s_Id
, Itq.ItemQtyItemExtCol as s_Col
, Itq.ItemQtyItemExtWs as s_Ws
From ItemQties as Itq
Left outer join (
Select
sum (ItS.ItemStockQty) as s1_Qty
, sum (Its.ItemStockOrdered) as s1_Ordered
, sum (Its.ItemStockReserved) as s1_Reserved
, sum( Its.ItemStockConsig) as s1_Consig
, sum( Its.ItemStockCall) as s1_Call
, sum( Its.ItemStockAsk) as s1_Ask
, sum( Its.ItemStockTransfer) as s1_Transfer
, sum( Its.ItemStockLabelQty) as s1_Label
, Itq.ItemQtyItemExt as s1_Id
, Itq.ItemQtyItemExtCol as s1_Col
, Itq.ItemQtyItemExtWs as s1_ws
from ItemStocks as Its
Left Outer Join ItemQties as Itq on ( Itq.ItemQtynumber = Its.ItemStockItemQty and itq.colroot = Its.ItemStockItemQtyCol and Itq.wsroot = Its.ItemStockItemQtyWs )
Left Outer Join ShopConnected as SC on ( SC.ShopLinked = Itq.ItemQtyCollect )
where SC.ShopVirtual = 1
Group by Itq.ItemQtyItemExt , Itq.ItemQtyItemExtCol , Itq.ItemQtyItemExtWs
) as s1 on ( s1_Id = Itq.ItemQtyItemExt and s1_Col = Itq.ItemQtyItemExtCol and s1_Ws = Itq.ItemQtyItemExtWs )
left outer join (
Select
Its.ItemStockQty as s2_Qty
, Its.ItemStockOrdered as s2_Ordered
, Its.ItemStockReserved as s2_REserved
, Its.ItemStockConsig as s2_Consig
, Its.ItemStockCall as s2_Call
, Its.ItemStockAsk as s2_Ask
, Its.ItemStockTransfer as s2_Transfer
, Its.ItemStockLabelQty as s2_Label
, Its.ItemStockMin as s2_Min
, Its.ItemStockMax as s2_Max
, Its.ItemStockPlace as s2_Place
, Its.ItemStockVolume as s2_Volume
, Its.ItemStockBrut as s2_Brut
, Its.ItemStockNet as s2_net
, Itq.ItemQtyItemExt as s2_Id
, Itq.ItemQtyItemExtCol as s2_Col
, Itq.ItemQtyItemExtWs as s2_Ws
From ITemStocks as Its
Left Outer Join ItemQties as itq on ( itq.ItemQtynumber = its.ItemStockItemQty and itq.colroot = its.ItemStockItemQtyCol and itq.wsroot = its.ItemStockItemQtyWs )
where Itq.ItemQtyCollect = 1 )
as s2 on ( s2_Id = Itq.ItemQtyItemExt and s2_Col = Itq.ItemQtyItemExtCol and s2_Ws = Itq.ItemQtyItemExtWs ) where Itq.ItemQtyCollect = 1
) as s on ( s_id = Ite.ItemExtnumber and s_col = Ite.colroot and s_ws = Ite.WsRoot )
Where Itm.ColDelete = 0 and Ite.ColDelete = 0 and ( (( (( Itm.ItemMainType = 0
)) )) )
Order By De_itm.MultiDescription Asc |
Problème de temps de réponse pour une recherche d'articles
, il faut aussi savoir qu'il peut y avoir plus de 100.000 articles dans la DB (chez certain client ça dépasse les 300.000 articles).
. Mais je suis pas sur que se soit la solution.
Répondre avec citation
Envoyé par Tazze-99
)
en créant une procédure qui construit l'sql dynamiquement, plutôt que 100 sql écrit en dur dans le programme. Dons je dois encore affiner la procedure.
Partager