Arjuna Aircraft Ident.: F-MBSD | sircam a écrit :
Citation :
De plus, faire une requête complexe en partant d'un schéma et de données abstraites, j'y arrive pas. Je ne peux réfléchir à un problème que si je peux me le représenter avec des règles absolues.
|
Beh oui. Faire des jointures entre T1, T2, T3 et T4 sur des champs F1, F2, F3 et F4 avec des index IDX1 et IDX2, c'est tout de suite nettement moins parlant.
|
Ben si...
Regarde ma requête de contrôle des stocks :
Code :
- select (
- select top 1 supplier_id
- from suppliement su
- where su.warehouse_id = st.warehouse_id
- and su.product_id = el.product_id
- and (
- price = (
- select min(price)
- from suppliement su2
- where su2.warehouse_id = su.warehouse_id
- and su2.product_id = su.product_id
- and su2.delay <= (
- select min(e.date_event)
- from
- city c,
- address a,
- event_line el,
- event e
- where e.type = 'ORD'
- and e.status = 'V'
- and el.event_id = e.id
- and el.status != 'R'
- and el.product_id = su2.product_id
- and a.user_id = e.user_id
- and a.type = 'LIV'
- and c.id = a.city
- and c.warehouse_id = su2.warehouse_id
- ) + 10
- and su2.max_supply >= (
- -1 * (
- st.stock - (
- sum(el.quantity) + st.reserved
- ) + isNull(
- (
- select sum(el2.quantity)
- from
- event_line el2,
- event e2
- where e2.type = 'PUR'
- and e2.warehouse_id = st.warehouse_id
- and e2.date_event >= @day - 30
- and el2.event_id = e2.id
- and el2.product_id = el.product_id
- )
- , 0)
- ) - st.qtysupply
- )
- )
- and su.max_supply >= (
- -1 * (
- st.stock - (
- sum(el.quantity) + st.reserved
- ) + isNull(
- (
- select sum(el2.quantity)
- from
- event_line el2,
- event e2
- where e2.type = 'PUR'
- and e2.warehouse_id = st.warehouse_id
- and e2.date_event >= @day - 30
- and el2.event_id = e2.id
- and el2.product_id = el.product_id
- )
- , 0)
- ) - st.qtysupply
- )
- or delay =
- (
- select min(delay)
- from suppliement su2
- where su2.warehouse_id = su.warehouse_id
- and su2.product_id = su.product_id
- and su2.delay >
- (
- select min(e.date_event)
- from
- city c,
- address a,
- event_line el,
- event e
- where e.type = 'ORD'
- and e.status = 'V'
- and el.event_id = e.id
- and el.status != 'R'
- and el.product_id = su2.product_id
- and a.user_id = e.user_id
- and a.type = 'LIV'
- and c.id = a.city
- and c.warehouse_id = su2.warehouse_id
- ) + 10
- and su2.max_supply >=
- (
- -1 *
- (
- st.stock -
- (
- sum(el.quantity) + st.reserved
- ) + isNull(
- (
- select sum(el2.quantity)
- from
- event_line el2,
- event e2
- where e2.type = 'PUR'
- and e2.warehouse_id = st.warehouse_id
- and e2.date_event >= @day - 30
- and el2.event_id = e2.id
- and el2.product_id = el.product_id
- )
- , 0)
- ) - st.qtysupply
- )
- and not exists
- (
- select null
- from suppliement su3
- where su3.warehouse_id = su2.warehouse_id
- and su3.product_id = su2.warehouse_id
- and su3.delay <=
- (
- select min(e.date_event)
- from
- city c,
- address a,
- event_line el,
- event e
- where e.type = 'ORD'
- and e.status = 'V'
- and el.event_id = e.id
- and el.status != 'R'
- and el.product_id = su3.product_id
- and a.user_id = e.user_id
- and a.type = 'LIV'
- and c.id = a.city
- and c.warehouse_id = su3.warehouse_id
- ) + 10
- )
- )
- and su.max_supply >= (
- -1 * (
- st.stock - (
- sum(el.quantity) + st.reserved
- ) + isNull(
- (
- select sum(el2.quantity)
- from
- event_line el2,
- event e2
- where e2.type = 'PUR'
- and e2.warehouse_id = st.warehouse_id
- and e2.date_event >= @day - 30
- and el2.event_id = e2.id
- and el2.product_id = el.product_id
- )
- , 0)
- ) - st.qtysupply
- )
- )
- ) supplier_id,
- st.warehouse_id,
- el.product_id,
- -1 *
- (
- st.stock -
- (
- sum(el.quantity) + st.reserved
- ) + isNull(
- (
- select sum(el2.quantity)
- from
- event_line el2,
- event e2
- where e2.type = 'PUR'
- and e2.status = 'V'
- and e2.warehouse_id = st.warehouse_id
- and el2.event_id = e2.id
- and el2.product_id = el.product_id
- )
- , 0)
- ) - st.qtysupply quantityPurchase
- from
- stock st,
- city ci,
- address a,
- event_line el,
- event e
- where e.status = 'V'
- and e.type = 'ORD'
- and el.event_id = e.id
- and el.status = 'V'
- and a.user_id = e.user_id
- and a.type = 'LIV'
- and ci.id = a.city
- and st.warehouse_id = ci.warehouse_id
- and st.product_id = el.product_id
- group by
- st.warehouse_id,
- el.product_id,
- st.reserved,
- st.stock,
- st.qtysupply
- having -1 *
- (
- st.stock -
- (
- sum(el.quantity) + st.reserved
- ) + isNull(
- (
- select sum(el2.quantity)
- from
- event_line el2,
- event e2
- where e2.type = 'PUR'
- and e2.status = 'V'
- and e2.warehouse_id = st.warehouse_id
- and el2.event_id = e2.id
- and el2.product_id = el.product_id
- )
- , 0)
- ) - st.qtysupply > 0
- order by
- supplier_id,
- st.warehouse_id
|
Ben en version T1.F1 (pas complètement, c'est chiant à faire), ça donne :
Code :
- select (
- select top 1 f1
- from t6 su
- where su.f2 = st.f1
- and su.f3 = el.f2
- and (
- price = (
- select min(price)
- from t6 su2
- where su2.f2 = su.f2
- and su2.f3 = su.f3
- and su2.delay <= (
- select min(e.date_t1)
- from
- t4 c,
- t3 a,
- t2 el,
- t1 e
- where e.type = 'ORD'
- and e.status = 'V'
- and el.t1_id = e.id
- and el.status != 'R'
- and el.f2 = su2.f3
- and a.user_id = e.user_id
- and a.type = 'LIV'
- and c.id = a.t4
- and c.f2 = su2.f2
- ) + 10
- and su2.max_supply >= (
- -1 * (
- st.t5 - (
- sum(el.quantity) + st.reserved
- ) + isNull(
- (
- select sum(el2.quantity)
- from
- t2 el2,
- t1 e2
- where e2.type = 'PUR'
- and e2.f4 = st.f1
- and e2.date_t1 >= @day - 30
- and el2.t1_id = e2.id
- and el2.f3 = el.f3
- )
- , 0)
- ) - st.qtysupply
- )
- )
- and su.max_supply >= (
- -1 * (
- st.t5 - (
- sum(el.quantity) + st.reserved
- ) + isNull(
- (
- select sum(el2.quantity)
- from
- t2 el2,
- t1 e2
- where e2.type = 'PUR'
- and e2.f4 = st.f1
- and e2.date_t1 >= @day - 30
- and el2.t1_id = e2.id
- and el2.f3 = el.f3
- )
- , 0)
- ) - st.qtysupply
- )
- or delay =
- (
- select min(delay)
- from t6 su2
- where su2.f2 = su.f2
- and su2.f3 = su.f3
- and su2.delay >
- (
- select min(e.date_t1)
- from
- t4 c,
- t3 a,
- t2 el,
- t1 e
- where e.type = 'ORD'
- and e.status = 'V'
- and el.t1_id = e.id
- and el.status != 'R'
- and el.f3 = su2.f3
- and a.user_id = e.user_id
- and a.type = 'LIV'
- and c.id = a.t4
- and c.f2 = su2.f2
- ) + 10
- and su2.max_supply >=
- (
- -1 *
- (
- st.t5 -
- (
- sum(el.quantity) + st.reserved
- ) + isNull(
- (
- select sum(el2.quantity)
- from
- t2 el2,
- t1 e2
- where e2.type = 'PUR'
- and e2.f4 = st.f1
- and e2.date_t1 >= @day - 30
- and el2.t1_id = e2.id
- and el2.f3 = el.f3
- )
- , 0)
- ) - st.qtysupply
- )
- and not exists
- (
- select null
- from t6 su3
- where su3.f2 = su2.f2
- and su3.f3 = su2.f3
- and su3.delay <=
- (
- select min(e.date_t1)
- from
- t4 c,
- t3 a,
- t2 el,
- t1 e
- where e.type = 'ORD'
- and e.status = 'V'
- and el.t1_id = e.id
- and el.status != 'R'
- and el.f3 = su3.f3
- and a.user_id = e.user_id
- and a.type = 'LIV'
- and c.id = a.t4
- and c.f2 = su3.f2
- ) + 10
- )
- )
- and su.max_supply >= (
- -1 * (
- st.t5 - (
- sum(el.quantity) + st.reserved
- ) + isNull(
- (
- select sum(el2.quantity)
- from
- t2 el2,
- t1 e2
- where e2.type = 'PUR'
- and e2.f4 = st.f1
- and e2.date_t1 >= @day - 30
- and el2.t1_id = e2.id
- and el2.f3 = el.f3
- )
- , 0)
- ) - st.qtysupply
- )
- )
- ) f1,
- st.f1,
- el.f3,
- -1 *
- (
- st.t5 -
- (
- sum(el.quantity) + st.reserved
- ) + isNull(
- (
- select sum(el2.quantity)
- from
- t2 el2,
- t1 e2
- where e2.type = 'PUR'
- and e2.status = 'V'
- and e2.f4 = st.f1
- and el2.t1_id = e2.id
- and el2.f3 = el.f3
- )
- , 0)
- ) - st.qtysupply quantityPurchase
- from
- t5 st,
- t4 ci,
- t3 a,
- t2 el,
- t1 e
- where e.status = 'V'
- and e.type = 'ORD'
- and el.t1_id = e.id
- and el.status = 'V'
- and a.user_id = e.user_id
- and a.type = 'LIV'
- and ci.id = a.t4
- and st.f1 = ci.f2
- and st.f2= el.f3
- group by
- st.f1,
- el.f3
- st.reserved,
- st.t5,
- st.qtysupply
- having -1 *
- (
- st.t5 -
- (
- sum(el.quantity) + st.reserved
- ) + isNull(
- (
- select sum(el2.quantity)
- from
- t2 el2,
- t1 e2
- where e2.type = 'PUR'
- and e2.status = 'V'
- and e2.f4 = st.f1
- and el2.t1_id = e2.id
- and el2.f3 el.f3
- )
- , 0)
- ) - st.qtysupply > 0
- order by
- f1,
- st.f1
|
Bah perso, autant la première, je la pond les yeux fermés à 3h du mat', et le lendemain j'arrive à peu près à m'y retrouver (j'ai d'ailleurs vu plein de bugs, faut que je corrige ça ce soir ), autant la seconde bah... |