 |
 |
Archives of the TeradataForum
Message Posted: Thu, 06 Nov 2003 @ 17:51:35 GMT
| Subj: | | Re: Insufficient memory to parse request error |
| |
| From: | | Ferry, Craig |
I am running V2R5.0.1.12.
I am referencing 7 tables and 1 view.
This is the query that is no longer running (which ran on V2R4)
select
i.branch_id, i.sim_mfr_no, i.sim_item_no, s.sim_description,
i.unit_cost, i.class_code, s.unit_of_measure_id,
f.factor, i.master_stock_item, i.leadtime, i.basic_stock,
i.order_review_point, i.fixed_review_point,
0.2*zeroifnull(sls_qty1)+0.16*zeroifnull(sls_qty2)+0.128*zeroifnull(sls_
qty3)+0.1024*zeroifnull(sls_qty4)+0.0819*zeroifnull(sls_qty5)+0.0655*zer
oifnull(sls_qty6)+0.0524*zeroifnull(sls_qty7)+0.0419*zeroifnull(sls_qty8
)+0.0336*zeroifnull(sls_qty9)+0.0268*zeroifnull(sls_qty10)+0.0215*zeroif
null(sls_qty11)+0.0172*zeroifnull(sls_qty12)+0.0137*zeroifnull(sls_qty13
)+0.011*zeroifnull(sls_qty14)+0.0088*zeroifnull(sls_qty15)+0.007*zeroifn
ull(sls_qty16)+0.0056*zeroifnull(sls_qty17)+0.0045*zeroifnull(sls_qty18)
+0.0036*zeroifnull(sls_qty19)+0.0029*zeroifnull(sls_qty20)+0.0023*zeroif
null(sls_qty21)+0.0018*zeroifnull(sls_qty22)+0.0015*zeroifnull(sls_qty23
)+0.0012*zeroifnull(sls_qty24) df ,
i.mad_forecast, i.date_first_receipt, i.date_last_issue ,
zeroifnull(sls_qty1)+zeroifnull(sls_qty2)+zeroifnull(sls_qty3)+zeroifnull(sls_qty4)+
zeroifnull(sls_qty5)+zeroifnull(sls_qty6)+zeroifnull(sls_qty7)+zeroifnull(sls_qty8)+
zeroifnull(sls_qty9)+zeroifnull(sls_qty10)+zeroifnull(sls_qty11)+zeroifnull(sls_qty12) last_12,
i.fixed_basic_stock, i.qty_on_backorder, i.qty_on_order,
i.qty_on_hand - i.qty_on_reserve qty_available,
(i.qty_on_hand - i.qty_on_reserve) * i.unit_cost * f.factor
qty_avail_dollars,
'2001/01/01' sales_day_1,
t.third_mo quantity_1,
'2001/01/01' sales_day_2,
t.third_mo quantity_2,
'2001/01/01' sales_day_3,
t.third_mo quantity_3,
d.no_days no_sales_days,
infor.leadtime_2,
case
when d.no_days > 20 and i.date_first_receipt <= cast('2003/06/01' as date) then 1
when d.no_days <= 20 and d.no_days >= 11 and i.date_first_receipt <=
cast('2003/06/01' as date) then 2
when d.no_days <= 10 and d.no_days >= 4 and i.date_first_receipt <=
cast('2003/06/01' as date) then 3
when d.no_days <= 3 and d.no_days >=1 and i.date_first_receipt <
cast('2003/04/01' as date) then 4
when (d.no_days =0 or d.no_days is null ) and i.date_first_receipt <
cast('2003/04/01' as date) then 5
when (d.no_days <=4 or d.no_days is null ) and (i.date_first_receipt =
is null or i.date_first_receipt >= cast('2003/04/01' as date) ) then 6
else 6
end vc,
case
when vc = 6 then i.order_review_point /* cast(qty_break_4/4 +.5 as integer) */
when vc = 4 or vc = 5 or vc = 0 then 0
when vc = 3 then (case
when zeroifnull(t.third_mo) >
last_12 / 4 then cast((case when group_id = 8100 then .7 else 1 end) *
cast(last_12 / 4 as dec(14,3)) as integer)
else cast((case when group_id = 8100
then .7 else 1 end) * cast(zeroifnull(t.third_mo) as dec(14,3)) as integer)
end )
when vc = 1 or vc = 2 then cast ( (case when group_id = 8100 then = .7
else 1 end) *
(((case when df < 0 then 0 else df end) * ((cast(leadtime_2 as
dec(16,3)) + 7) / 30))
+ (2.06 * i.mad_forecast * sqrt( (cast(leadtime_2 as
dec ( 16,2))+ 7) / 30 )) + .5)
as integer )
end new_orp,
case
when vc = 6 then i.basic_stock
when vc <> 6 and vc <> 1 and vc <> 2 then 0
else
(case
when last_12 > 0 then
(case
when i.unit_cost > 0 then
(case
when sqrt( (10.00 * last_12) / (.29 * i.unit_cost *
f.factor)) < (last_12 * .038) + .5 then cast( ((case when group_id =
8100 then .7 else 1 end) * (last_12 * .038) + .5) as integer)
when sqrt( (10.00 * last_12) / (.29 * i.unit_cost *
f.factor)) > last_12 / 6 then cast((case when group_id = 8100 then .7
else 1 end) * (last_12 / 6) as integer)
else cast((case when group_id = 8100 then .7 else 1 end) =
*
(sqrt( (10.00 * last_12) / (.29 * i.unit_cost * f.factor)) + .5) as integer)
end)
else 0
end)
else 0
end)
end new_eoq,
0 est_impact_dollars,
t.third_mo sales_3mo,
0 mgn_pct_3mo,
' ' /* infor.dc_stock */,
0 fin_chg_pct,
'2003/09/01' date_loaded
FROM dss_tables.dly_inventory i inner join branch b on i.branch_id = b.branch_id
inner join (select s.*,
case
when asr_pack_qty > 0 then asr_pack_qty
when asr_pack_qty = 0 and qty_break_2 > 0 then qty_break_2
when asr_pack_qty = 0 and qty_break_2 = 0 then qty_break_3
end qty_break_4
from style_bob s where country_code = '1') s
on i.sim_mfr_no = s.sim_mfr_no and i.sim_item_no = s.sim_item_no and
i.country_code = s.country_code
inner join dss_tables.unit_of_measure f
on s.unit_of_measure_id = f.unit_of_measure_id
left outer join (SELECT
BRANCH_ID,
SIM_MFR_NO,
SIM_ITEM_NO,
COUNTRY_CODE,
SUM(CASE WHEN DATE_LOADED = cast('2003/08/01' as date) THEN ZEROIFNULL
(SLS_QTY) ELSE 0 END) SLS_QTY1,
SUM(CASE WHEN DATE_LOADED = cast('2003/07/01' as date) THEN ZEROIFNULL
(SLS_QTY) ELSE 0 END) SLS_QTY2,
SUM(CASE WHEN DATE_LOADED = cast('2003/06/01' as date) THEN ZEROIFNULL
(SLS_QTY) ELSE 0 END) SLS_QTY3,
SUM(CASE WHEN DATE_LOADED = cast('2003/05/01' as date) THEN ZEROIFNULL
(SLS_QTY) ELSE 0 END) SLS_QTY4,
SUM(CASE WHEN DATE_LOADED = cast('2003/04/01' as date) THEN ZEROIFNULL
(SLS_QTY) ELSE 0 END) SLS_QTY5,
SUM(CASE WHEN DATE_LOADED = cast('2003/03/01' as date) THEN ZEROIFNULL
(SLS_QTY) ELSE 0 END) SLS_QTY6,
SUM(CASE WHEN DATE_LOADED = cast('2003/02/01' as date) THEN ZEROIFNULL
(SLS_QTY) ELSE 0 END) SLS_QTY7,
SUM(CASE WHEN DATE_LOADED = cast('2003/01/01' as date) THEN ZEROIFNULL
(SLS_QTY) ELSE 0 END) SLS_QTY8,
SUM(CASE WHEN DATE_LOADED = cast('2002/12/01' as date) THEN ZEROIFNULL
(SLS_QTY) ELSE 0 END) SLS_QTY9,
SUM(CASE WHEN DATE_LOADED = cast('2002/11/01' as date) THEN ZEROIFNULL
(SLS_QTY) ELSE 0 END) SLS_QTY10,
SUM(CASE WHEN DATE_LOADED = cast('2002/10/01' as date) THEN ZEROIFNULL
(SLS_QTY) ELSE 0 END) SLS_QTY11,
SUM(CASE WHEN DATE_LOADED = cast('2002/09/01' as date) THEN ZEROIFNULL
(SLS_QTY) ELSE 0 END) SLS_QTY12,
SUM(CASE WHEN DATE_LOADED = cast('2002/08/01' as date) THEN ZEROIFNULL
(SLS_QTY) ELSE 0 END) SLS_QTY13,
SUM(CASE WHEN DATE_LOADED = cast('2002/07/01' as date) THEN ZEROIFNULL
(SLS_QTY) ELSE 0 END) SLS_QTY14,
SUM(CASE WHEN DATE_LOADED = cast('2002/06/01' as date) THEN ZEROIFNULL
(SLS_QTY) ELSE 0 END) SLS_QTY15,
SUM(CASE WHEN DATE_LOADED = cast('2002/05/01' as date) THEN ZEROIFNULL
(SLS_QTY) ELSE 0 END) SLS_QTY16,
SUM(CASE WHEN DATE_LOADED = cast('2002/04/01' as date) THEN ZEROIFNULL
(SLS_QTY) ELSE 0 END) SLS_QTY17,
SUM(CASE WHEN DATE_LOADED = cast('2002/03/01' as date) THEN ZEROIFNULL
(SLS_QTY) ELSE 0 END) SLS_QTY18,
SUM(CASE WHEN DATE_LOADED = cast('2002/02/01' as date) THEN ZEROIFNULL
(SLS_QTY) ELSE 0 END) SLS_QTY19,
SUM(CASE WHEN DATE_LOADED = cast('2002/01/01' as date) THEN ZEROIFNULL
(SLS_QTY) ELSE 0 END) SLS_QTY20,
SUM(CASE WHEN DATE_LOADED = cast('2001/12/01' as date) THEN ZEROIFNULL
(SLS_QTY) ELSE 0 END) SLS_QTY21,
SUM(CASE WHEN DATE_LOADED = cast('2001/11/01' as date) THEN ZEROIFNULL
(SLS_QTY) ELSE 0 END) SLS_QTY22,
SUM(CASE WHEN DATE_LOADED = cast('2001/10/01' as date) THEN ZEROIFNULL
(SLS_QTY) ELSE 0 END) SLS_QTY23,
SUM(CASE WHEN DATE_LOADED = cast('2001/09/01' as date) THEN ZEROIFNULL
(SLS_QTY) ELSE 0 END) SLS_QTY24
FROM DSSBATCH.MKGAPS_QTY
WHERE
SELECT_STAT = '1' AND
branch_id in (select branch_id from branch where group_id in
(8100,8500,8800) )
GROUP BY 1,2,3,4
) p
on i.branch_id = p.branch_id and i.sim_mfr_no = p.sim_mfr_no and
i.sim_item_no = p.sim_item_no
left outer join (select branch_id, sim_mfr_no, sim_item_no,
count(distinct date_invoice) no_days from dssbatch.mkgaps_qty where
date_loaded between cast('2002/10/01' as date) and cast('2003/09/01' as
date) group by 1,2,3 ) as d
on i.branch_id = d.branch_id and i.sim_mfr_no = d.sim_mfr_no and
i.sim_item_no = d.sim_item_no
left outer join (SELECT branch_id, sim_mfr_no, sim_item_no,
case
when zeroifnull(sls_qty1) >= zeroifnull(sls_qty2) and
zeroifnull(sls_qty1) >= zeroifnull(sls_qty3) and zeroifnull(sls_qty1)
>= zeroifnull(sls_qty4) and zeroifnull(sls_qty1) >=
zeroifnull(sls_qty5) and zeroifnull(sls_qty1) >= zeroifnull(sls_qty6)
and zeroifnull(sls_qty1) >= zeroifnull(sls_qty7) and
zeroifnull(sls_qty1) >= zeroifnull(sls_qty8) and zeroifnull(sls_qty1)
>= zeroifnull(sls_qty9 ) and zeroifnull(sls_qty1) >=
zeroifnull(sls_qty10) and zeroifnull(sls_qty1) >= =
zeroifnull(sls_qty11)
and zeroifnull(sls_qty1) >= zeroifnull(sls_qty12) then sls_qty1
when zeroifnull(sls_qty2) > zeroifnull(sls_qty1) and
zeroifnull(sls_qty2) >= zeroifnull(sls_qty3) and zeroifnull(sls_qty2)
>= zeroifnull(sls_qty4) and zeroifnull(sls_qty2) >=
zeroifnull(sls_qty5) and zeroifnull(sls_qty2) >= zeroifnull(sls_qty6)
and zeroifnull(sls_qty2) >= zeroifnull(sls_qty7) and
zeroifnull(sls_qty2) >= zeroifnull(sls_qty8) and zeroifnull(sls_qty2)
>= zeroifnull(sls_qty9 ) and zeroifnull(sls_qty2) >=
zeroifnull(sls_qty10) and zeroifnull(sls_qty2) >= =
zeroifnull(sls_qty11)
and zeroifnull(sls_qty2) >= zeroifnull(sls_qty12) then sls_qty2
when zeroifnull(sls_qty3) > zeroifnull(sls_qty2) and
zeroifnull(sls_qty3) > zeroifnull(sls_qty1) and zeroifnull(sls_qty3)
>= zeroifnull(sls_qty4) and zeroifnull(sls_qty3) >=
zeroifnull(sls_qty5) and zeroifnull(sls_qty3) >= zeroifnull(sls_qty6)
and zeroifnull(sls_qty3) >= zeroifnull(sls_qty7) and
zeroifnull(sls_qty3) >= zeroifnull(sls_qty8) and zeroifnull(sls_qty3)
>= zeroifnull(sls_qty9 ) and zeroifnull(sls_qty3) >=
zeroifnull(sls_qty10) and zeroifnull(sls_qty3) >= =
zeroifnull(sls_qty11)
and zeroifnull(sls_qty3) >= zeroifnull(sls_qty12) then sls_qty3
when zeroifnull(sls_qty4) > zeroifnull(sls_qty2) and
zeroifnull(sls_qty4) > zeroifnull(sls_qty3) and zeroifnull(sls_qty4)
> zeroifnull(sls_qty1) and zeroifnull(sls_qty4) >=
zeroifnull(sls_qty5) and zeroifnull(sls_qty4) >= zeroifnull(sls_qty6)
and zeroifnull(sls_qty4) >= zeroifnull(sls_qty7) and
zeroifnull(sls_qty4) >= zeroifnull(sls_qty8) and zeroifnull(sls_qty4)
>= zeroifnull(sls_qty9 ) and zeroifnull(sls_qty4) >=
zeroifnull(sls_qty10) and zeroifnull(sls_qty4) >= =
zeroifnull(sls_qty11)
and zeroifnull(sls_qty4) >= zeroifnull(sls_qty12) then sls_qty4
when zeroifnull(sls_qty5) > zeroifnull(sls_qty2) and
zeroifnull(sls_qty5) > zeroifnull(sls_qty3) and zeroifnull(sls_qty5)
> zeroifnull(sls_qty4) and zeroifnull(sls_qty5) >
zeroifnull(sls_qty1) and zeroifnull(sls_qty5) >= zeroifnull(sls_qty6)
and zeroifnull(sls_qty5) >= zeroifnull(sls_qty7) and
zeroifnull(sls_qty5) >= zeroifnull(sls_qty8) and zeroifnull(sls_qty5)
>= zeroifnull(sls_qty9 ) and zeroifnull(sls_qty5) >=
zeroifnull(sls_qty10) and zeroifnull(sls_qty5) >= =
zeroifnull(sls_qty11)
and zeroifnull(sls_qty5) >= zeroifnull(sls_qty12) then sls_qty5
when zeroifnull(sls_qty6) > zeroifnull(sls_qty2) and
zeroifnull(sls_qty6) > zeroifnull(sls_qty3) and zeroifnull(sls_qty6)
> zeroifnull(sls_qty4) and zeroifnull(sls_qty6) >
zeroifnull(sls_qty5) and zeroifnull(sls_qty6) > zeroifnull(sls_qty1)
and zeroifnull(sls_qty6) >= zeroifnull(sls_qty7) and
zeroifnull(sls_qty6) >= zeroifnull(sls_qty8) and zeroifnull(sls_qty6)
>= zeroifnull(sls_qty9 ) and zeroifnull(sls_qty6) >=
zeroifnull(sls_qty10) and zeroifnull(sls_qty6) >= =
zeroifnull(sls_qty11)
and zeroifnull(sls_qty6) >= zeroifnull(sls_qty12) then sls_qty6
when zeroifnull(sls_qty7) > zeroifnull(sls_qty2) and
zeroifnull(sls_qty7) > zeroifnull(sls_qty3) and zeroifnull(sls_qty7)
> zeroifnull(sls_qty4) and zeroifnull(sls_qty7) >
zeroifnull(sls_qty5) and zeroifnull(sls_qty7) > zeroifnull(sls_qty6)
and zeroifnull(sls_qty7) > zeroifnull(sls_qty1) and
zeroifnull(sls_qty7) >= zeroifnull(sls_qty8) and zeroifnull(sls_qty7)
>= zeroifnull(sls_qty9 ) and zeroifnull(sls_qty7) >=
zeroifnull(sls_qty10) and zeroifnull(sls_qty7) >= =
zeroifnull(sls_qty11)
and zeroifnull(sls_qty7) >= zeroifnull(sls_qty12) then sls_qty7
when zeroifnull(sls_qty8) > zeroifnull(sls_qty2) and
zeroifnull(sls_qty8) > zeroifnull(sls_qty3) and zeroifnull(sls_qty8)
> zeroifnull(sls_qty4) and zeroifnull(sls_qty8) >
zeroifnull(sls_qty5) and zeroifnull(sls_qty8) > zeroifnull(sls_qty6)
and zeroifnull(sls_qty8) > zeroifnull(sls_qty7) and
zeroifnull(sls_qty8) > zeroifnull(sls_qty1) and zeroifnull(sls_qty8)
>= zeroifnull(sls_qty9 ) and zeroifnull(sls_qty8) >=
zeroifnull(sls_qty10) and zeroifnull(sls_qty8) >= =
zeroifnull(sls_qty11)
and zeroifnull(sls_qty8) >= zeroifnull(sls_qty12) then sls_qty8
when zeroifnull(sls_qty9) > zeroifnull(sls_qty2) and
zeroifnull(sls_qty9) > zeroifnull(sls_qty3) and zeroifnull(sls_qty9)
> zeroifnull(sls_qty4) and zeroifnull(sls_qty9) >
zeroifnull(sls_qty5) and zeroifnull(sls_qty9) > zeroifnull(sls_qty6)
and zeroifnull(sls_qty9) > zeroifnull(sls_qty7) and
zeroifnull(sls_qty9) > zeroifnull(sls_qty8) and zeroifnull(sls_qty9)
> zeroifnull(sls_qty1) and zeroifnull(sls_qty9) >=
zeroifnull(sls_qty10) and zeroifnull(sls_qty9) >= =
zeroifnull(sls_qty11)
and zeroifnull(sls_qty9) >= zeroifnull(sls_qty12) then sls_qty9
when zeroifnull(sls_qty10) > zeroifnull(sls_qty2) and
zeroifnull(sls_qty10) > zeroifnull(sls_qty3) and zeroifnull(sls_qty10)
> zeroifnull(sls_qty4) and zeroifnull(sls_qty10) >
zeroifnull(sls_qty5) and zeroifnull(sls_qty10) > zeroifnull(sls_qty6)
and zeroifnull(sls_qty10) > zeroifnull(sls_qty7) and
zeroifnull(sls_qty10) > zeroifnull(sls_qty8) and zeroifnull(sls_qty10)
> zeroifnull(sls_qty9 ) and zeroifnull(sls_qty10) >
zeroifnull(sls_qty1) and zeroifnull(sls_qty10) >= =
zeroifnull(sls_qty11)
and zeroifnull(sls_qty10) >= zeroifnull(sls_qty12) then sls_qty10
when zeroifnull(sls_qty11) > zeroifnull(sls_qty2) and
zeroifnull(sls_qty11) > zeroifnull(sls_qty3) and zeroifnull(sls_qty11)
> zeroifnull(sls_qty4) and zeroifnull(sls_qty11) >
zeroifnull(sls_qty5) and zeroifnull(sls_qty11) > zeroifnull(sls_qty6)
and zeroifnull(sls_qty11) > zeroifnull(sls_qty7) and
zeroifnull(sls_qty11) > zeroifnull(sls_qty8) and zeroifnull(sls_qty11)
> zeroifnull(sls_qty9 ) and zeroifnull(sls_qty11) >
zeroifnull(sls_qty10) and zeroifnull(sls_qty11) > zeroifnull(sls_qty1)
and zeroifnull(sls_qty11) >= zeroifnull(sls_qty12) then sls_qty11
when zeroifnull(sls_qty12) > zeroifnull(sls_qty2) and
zeroifnull(sls_qty12) > zeroifnull(sls_qty3) and zeroifnull(sls_qty12)
> zeroifnull(sls_qty4) and zeroifnull(sls_qty12) >
zeroifnull(sls_qty5) and zeroifnull(sls_qty12) > zeroifnull(sls_qty6)
and zeroifnull(sls_qty12) > zeroifnull(sls_qty7) and
zeroifnull(sls_qty12) > zeroifnull(sls_qty8) and zeroifnull(sls_qty12)
> zeroifnull(sls_qty9 ) and zeroifnull(sls_qty12) >
zeroifnull(sls_qty10) and zeroifnull(sls_qty12) >
zeroifnull(sls_qty11) and zeroifnull(sls_qty12) > zeroifnull(sls_qty1)
then sls_qty12
end third_mo
from (
select branch_id, sim_mfr_no, sim_item_no,
case when zeroifnull(sls_qty1) >= zeroifnull(sls_qty2) and
zeroifnull(sls_qty1) >= zeroifnull(sls_qty3) and zeroifnull(sls_qty1)
>= zeroifnull(sls_qty4) and zeroifnull(sls_qty1) >=
zeroifnull(sls_qty5) and zeroifnull(sls_qty1) >= zeroifnull(sls_qty6)
and zeroifnull(sls_qty1) >= zeroifnull(sls_qty7) and
zeroifnull(sls_qty1) >= zeroifnull(sls_qty8) and zeroifnull(sls_qty1)
>= zeroifnull(sls_qty9 ) and zeroifnull(sls_qty1) >=
zeroifnull(sls_qty10) and zeroifnull(sls_qty1) >= =
zeroifnull(sls_qty11)
and zeroifnull(sls_qty1) >= zeroifnull(sls_qty12) then -99999999 =
else
sls_qty1 end sls_qty1,
case when zeroifnull(sls_qty2) > zeroifnull(sls_qty1) and
zeroifnull(sls_qty2) >= zeroifnull(sls_qty3) and zeroifnull(sls_qty2)
>= zeroifnull(sls_qty4) and zeroifnull(sls_qty2) >=
zeroifnull(sls_qty5) and zeroifnull(sls_qty2) >= zeroifnull(sls_qty6)
and zeroifnull(sls_qty2) >= zeroifnull(sls_qty7) and
zeroifnull(sls_qty2) >= zeroifnull(sls_qty8) and zeroifnull(sls_qty2)
>= zeroifnull(sls_qty9 ) and zeroifnull(sls_qty2) >=
zeroifnull(sls_qty10) and zeroifnull(sls_qty2) >= =
zeroifnull(sls_qty11)
and zeroifnull(sls_qty2) >= zeroifnull(sls_qty12) then -99999999 =
else
sls_qty2 end sls_qty2,
case when zeroifnull(sls_qty3) > zeroifnull(sls_qty2) and
zeroifnull(sls_qty3) > zeroifnull(sls_qty1) and zeroifnull(sls_qty3)
>= zeroifnull(sls_qty4) and zeroifnull(sls_qty3) >=
zeroifnull(sls_qty5) and zeroifnull(sls_qty3) >= zeroifnull(sls_qty6)
and zeroifnull(sls_qty3) >= zeroifnull(sls_qty7) and
zeroifnull(sls_qty3) >= zeroifnull(sls_qty8) and zeroifnull(sls_qty3)
>= zeroifnull(sls_qty9 ) and zeroifnull(sls_qty3) >=
zeroifnull(sls_qty10) and zeroifnull(sls_qty3) >= =
zeroifnull(sls_qty11)
and zeroifnull(sls_qty3) >= zeroifnull(sls_qty12) then -99999999 =
else
sls_qty3 end sls_qty3,
case when zeroifnull(sls_qty4) > zeroifnull(sls_qty2) and
zeroifnull(sls_qty4) > zeroifnull(sls_qty3) and zeroifnull(sls_qty4)
> zeroifnull(sls_qty1) and zeroifnull(sls_qty4) >=
zeroifnull(sls_qty5) and zeroifnull(sls_qty4) >= zeroifnull(sls_qty6)
and zeroifnull(sls_qty4) >= zeroifnull(sls_qty7) and
zeroifnull(sls_qty4) >= zeroifnull(sls_qty8) and zeroifnull(sls_qty4)
>= zeroifnull(sls_qty9 ) and zeroifnull(sls_qty4) >=
zeroifnull(sls_qty10) and zeroifnull(sls_qty4) >= =
zeroifnull(sls_qty11)
and zeroifnull(sls_qty4) >= zeroifnull(sls_qty12) then -99999999 =
else
sls_qty4 end sls_qty4,
case when zeroifnull(sls_qty5) > zeroifnull(sls_qty2) and
zeroifnull(sls_qty5) > zeroifnull(sls_qty3) and zeroifnull(sls_qty5)
> zeroifnull(sls_qty4) and zeroifnull(sls_qty5) >
zeroifnull(sls_qty1) and zeroifnull(sls_qty5) >= zeroifnull(sls_qty6)
and zeroifnull(sls_qty5) >= zeroifnull(sls_qty7) and
zeroifnull(sls_qty5) >= zeroifnull(sls_qty8) and zeroifnull(sls_qty5)
>= zeroifnull(sls_qty9 ) and zeroifnull(sls_qty5) >=
zeroifnull(sls_qty10) and zeroifnull(sls_qty5) >= =
zeroifnull(sls_qty11)
and zeroifnull(sls_qty5) >= zeroifnull(sls_qty12) then -99999999 =
else
sls_qty5 end sls_qty5,
case when zeroifnull(sls_qty6) > zeroifnull(sls_qty2) and
zeroifnull(sls_qty6) > zeroifnull(sls_qty3) and zeroifnull(sls_qty6)
> zeroifnull(sls_qty4) and zeroifnull(sls_qty6) >
zeroifnull(sls_qty5) and zeroifnull(sls_qty6) > zeroifnull(sls_qty1)
and zeroifnull(sls_qty6) >= zeroifnull(sls_qty7) and
zeroifnull(sls_qty6) >= zeroifnull(sls_qty8) and zeroifnull(sls_qty6)
>= zeroifnull(sls_qty9 ) and zeroifnull(sls_qty6) >=
zeroifnull(sls_qty10) and zeroifnull(sls_qty6) >= =
zeroifnull(sls_qty11)
and zeroifnull(sls_qty6) >= zeroifnull(sls_qty12) then -99999999 =
else
sls_qty6 end sls_qty6,
case when zeroifnull(sls_qty7) > zeroifnull(sls_qty2) and
zeroifnull(sls_qty7) > zeroifnull(sls_qty3) and zeroifnull(sls_qty7)
> zeroifnull(sls_qty4) and zeroifnull(sls_qty7) >
zeroifnull(sls_qty5) and zeroifnull(sls_qty7) > zeroifnull(sls_qty6)
and zeroifnull(sls_qty7) > zeroifnull(sls_qty1) and
zeroifnull(sls_qty7) >= zeroifnull(sls_qty8) and zeroifnull(sls_qty7)
>= zeroifnull(sls_qty9 ) and zeroifnull(sls_qty7) >=
zeroifnull(sls_qty10) and zeroifnull(sls_qty7) >= =
zeroifnull(sls_qty11)
and zeroifnull(sls_qty7) >= zeroifnull(sls_qty12) then -99999999 =
else
sls_qty7 end sls_qty7,
case when zeroifnull(sls_qty8) > zeroifnull(sls_qty2) and
zeroifnull(sls_qty8) > zeroifnull(sls_qty3) and zeroifnull(sls_qty8)
> zeroifnull(sls_qty4) and zeroifnull(sls_qty8) >
zeroifnull(sls_qty5) and zeroifnull(sls_qty8) > zeroifnull(sls_qty6)
and zeroifnull(sls_qty8) > zeroifnull(sls_qty7) and
zeroifnull(sls_qty8) > zeroifnull(sls_qty1) and zeroifnull(sls_qty8)
>= zeroifnull(sls_qty9 ) and zeroifnull(sls_qty8) >=
zeroifnull(sls_qty10) and zeroifnull(sls_qty8) >= =
zeroifnull(sls_qty11)
and zeroifnull(sls_qty8) >= zeroifnull(sls_qty12) then -99999999 =
else
sls_qty8 end sls_qty8,
case when zeroifnull(sls_qty9) > zeroifnull(sls_qty2) and
zeroifnull(sls_qty9) > zeroifnull(sls_qty3) and zeroifnull(sls_qty9)
> zeroifnull(sls_qty4) and zeroifnull(sls_qty9) >
zeroifnull(sls_qty5) and zeroifnull(sls_qty9) > zeroifnull(sls_qty6)
and zeroifnull(sls_qty9) > zeroifnull(sls_qty7) and
zeroifnull(sls_qty9) > zeroifnull(sls_qty8) and zeroifnull(sls_qty9)
> zeroifnull(sls_qty1) and zeroifnull(sls_qty9) >=
zeroifnull(sls_qty10) and zeroifnull(sls_qty9) >= =
zeroifnull(sls_qty11)
and zeroifnull(sls_qty9) >= zeroifnull(sls_qty12) then -99999999 =
else
sls_qty9 end sls_qty9,
case when zeroifnull(sls_qty10) > zeroifnull(sls_qty2) and
zeroifnull(sls_qty10) > zeroifnull(sls_qty3) and zeroifnull(sls_qty10)
> zeroifnull(sls_qty4) and zeroifnull(sls_qty10) >
zeroifnull(sls_qty5) and zeroifnull(sls_qty10) > zeroifnull(sls_qty6)
and zeroifnull(sls_qty10) > zeroifnull(sls_qty7) and
zeroifnull(sls_qty10) > zeroifnull(sls_qty8) and zeroifnull(sls_qty10)
> zeroifnull(sls_qty9 ) and zeroifnull(sls_qty10) >
zeroifnull(sls_qty1) and zeroifnull(sls_qty10) >= =
zeroifnull(sls_qty11)
and zeroifnull(sls_qty10) >= zeroifnull(sls_qty12) then -99999999 =
else
sls_qty10 end sls_qty10,
case when zeroifnull(sls_qty11) > zeroifnull(sls_qty2) and
zeroifnull(sls_qty11) > zeroifnull(sls_qty3) and zeroifnull(sls_qty11)
> zeroifnull(sls_qty4) and zeroifnull(sls_qty11) >
zeroifnull(sls_qty5) and zeroifnull(sls_qty11) > zeroifnull(sls_qty6)
and zeroifnull(sls_qty11) > zeroifnull(sls_qty7) and
zeroifnull(sls_qty11) > zeroifnull(sls_qty8) and zeroifnull(sls_qty11)
> zeroifnull(sls_qty9 ) and zeroifnull(sls_qty11) >
zeroifnull(sls_qty10) and zeroifnull(sls_qty11) > zeroifnull(sls_qty1)
and zeroifnull(sls_qty11) >= zeroifnull(sls_qty12) then -99999999 =
else
sls_qty11 end sls_qty11,
case when zeroifnull(sls_qty12) > zeroifnull(sls_qty2) and
zeroifnull(sls_qty12) > zeroifnull(sls_qty3) and zeroifnull(sls_qty12)
> zeroifnull(sls_qty4) and zeroifnull(sls_qty12) >
zeroifnull(sls_qty5) and zeroifnull(sls_qty12) > zeroifnull(sls_qty6)
and zeroifnull(sls_qty12) > zeroifnull(sls_qty7) and
zeroifnull(sls_qty12) > zeroifnull(sls_qty8) and zeroifnull(sls_qty12)
> zeroifnull(sls_qty9 ) and zeroifnull(sls_qty12) >
zeroifnull(sls_qty10) and zeroifnull(sls_qty12) >
zeroifnull(sls_qty11) and zeroifnull(sls_qty12) > zeroifnull(sls_qty1)
then -99999999 else sls_qty12 end sls_qty12
from (
select branch_id, sim_mfr_no, sim_item_no,
case when zeroifnull(sls_qty1) >= zeroifnull(sls_qty2) and
zeroifnull(sls_qty1) >= zeroifnull(sls_qty3) and zeroifnull(sls_qty1)
>= zeroifnull(sls_qty4) and zeroifnull(sls_qty1) >=
zeroifnull(sls_qty5) and zeroifnull(sls_qty1) >= zeroifnull(sls_qty6)
and zeroifnull(sls_qty1) >= zeroifnull(sls_qty7) and
zeroifnull(sls_qty1) >= zeroifnull(sls_qty8) and zeroifnull(sls_qty1)
>= zeroifnull(sls_qty9 ) and zeroifnull(sls_qty1) >=
zeroifnull(sls_qty10) and zeroifnull(sls_qty1) >= =
zeroifnull(sls_qty11)
and zeroifnull(sls_qty1) >= zeroifnull(sls_qty12) then -99999999 =
else
sls_qty1 end sls_qty1,
case when zeroifnull(sls_qty2) > zeroifnull(sls_qty1) and
zeroifnull(sls_qty2) >= zeroifnull(sls_qty3) and zeroifnull(sls_qty2)
>= zeroifnull(sls_qty4) and zeroifnull(sls_qty2) >=
zeroifnull(sls_qty5) and zeroifnull(sls_qty2) >= zeroifnull(sls_qty6)
and zeroifnull(sls_qty2) >= zeroifnull(sls_qty7) and
zeroifnull(sls_qty2) >= zeroifnull(sls_qty8) and zeroifnull(sls_qty2)
>= zeroifnull(sls_qty9 ) and zeroifnull(sls_qty2) >=
zeroifnull(sls_qty10) and zeroifnull(sls_qty2) >= =
zeroifnull(sls_qty11)
and zeroifnull(sls_qty2) >= zeroifnull(sls_qty12) then -99999999 =
else
sls_qty2 end sls_qty2,
case when zeroifnull(sls_qty3) > zeroifnull(sls_qty2) and
zeroifnull(sls_qty3) > zeroifnull(sls_qty1) and zeroifnull(sls_qty3)
>= zeroifnull(sls_qty4) and zeroifnull(sls_qty3) >=
zeroifnull(sls_qty5) and zeroifnull(sls_qty3) >= zeroifnull(sls_qty6)
and zeroifnull(sls_qty3) >= zeroifnull(sls_qty7) and
zeroifnull(sls_qty3) >= zeroifnull(sls_qty8) and zeroifnull(sls_qty3)
>= zeroifnull(sls_qty9 ) and zeroifnull(sls_qty3) >=
zeroifnull(sls_qty10) and zeroifnull(sls_qty3) >= =
zeroifnull(sls_qty11)
and zeroifnull(sls_qty3) >= zeroifnull(sls_qty12) then -99999999 =
else
sls_qty3 end sls_qty3,
case when zeroifnull(sls_qty4) > zeroifnull(sls_qty2) and
zeroifnull(sls_qty4) > zeroifnull(sls_qty3) and zeroifnull(sls_qty4)
> zeroifnull(sls_qty1) and zeroifnull(sls_qty4) >=
zeroifnull(sls_qty5) and zeroifnull(sls_qty4) >= zeroifnull(sls_qty6)
and zeroifnull(sls_qty4) >= zeroifnull(sls_qty7) and
zeroifnull(sls_qty4) >= zeroifnull(sls_qty8) and zeroifnull(sls_qty4)
>= zeroifnull(sls_qty9 ) and zeroifnull(sls_qty4) >=
zeroifnull(sls_qty10) and zeroifnull(sls_qty4) >= =
zeroifnull(sls_qty11)
and zeroifnull(sls_qty4) >= zeroifnull(sls_qty12) then -99999999 =
else
sls_qty4 end sls_qty4,
case when zeroifnull(sls_qty5) > zeroifnull(sls_qty2) and
zeroifnull(sls_qty5) > zeroifnull(sls_qty3) and zeroifnull(sls_qty5)
> zeroifnull(sls_qty4) and zeroifnull(sls_qty5) >
zeroifnull(sls_qty1) and zeroifnull(sls_qty5) >= zeroifnull(sls_qty6)
and zeroifnull(sls_qty5) >= zeroifnull(sls_qty7) and
zeroifnull(sls_qty5) >= zeroifnull(sls_qty8) and zeroifnull(sls_qty5)
>= zeroifnull(sls_qty9 ) and zeroifnull(sls_qty5) >=
zeroifnull(sls_qty10) and zeroifnull(sls_qty5) >= =
zeroifnull(sls_qty11)
and zeroifnull(sls_qty5) >= zeroifnull(sls_qty12) then -99999999 =
else
sls_qty5 end sls_qty5,
case when zeroifnull(sls_qty6) > zeroifnull(sls_qty2) and
zeroifnull(sls_qty6) > zeroifnull(sls_qty3) and zeroifnull(sls_qty6)
> zeroifnull(sls_qty4) and zeroifnull(sls_qty6) >
zeroifnull(sls_qty5) and zeroifnull(sls_qty6) > zeroifnull(sls_qty1)
and zeroifnull(sls_qty6) >= zeroifnull(sls_qty7) and
zeroifnull(sls_qty6) >= zeroifnull(sls_qty8) and zeroifnull(sls_qty6)
>= zeroifnull(sls_qty9 ) and zeroifnull(sls_qty6) >=
zeroifnull(sls_qty10) and zeroifnull(sls_qty6) >= =
zeroifnull(sls_qty11)
and zeroifnull(sls_qty6) >= zeroifnull(sls_qty12) then -99999999 =
else
sls_qty6 end sls_qty6,
case when zeroifnull(sls_qty7) > zeroifnull(sls_qty2) and
zeroifnull(sls_qty7) > zeroifnull(sls_qty3) and zeroifnull(sls_qty7)
> zeroifnull(sls_qty4) and zeroifnull(sls_qty7) >
zeroifnull(sls_qty5) and zeroifnull(sls_qty7) > zeroifnull(sls_qty6)
and zeroifnull(sls_qty7) > zeroifnull(sls_qty1) and
zeroifnull(sls_qty7) >= zeroifnull(sls_qty8) and zeroifnull(sls_qty7)
>= zeroifnull(sls_qty9 ) and zeroifnull(sls_qty7) >=
zeroifnull(sls_qty10) and zeroifnull(sls_qty7) >= =
zeroifnull(sls_qty11)
and zeroifnull(sls_qty7) >= zeroifnull(sls_qty12) then -99999999 =
else
sls_qty7 end sls_qty7,
case when zeroifnull(sls_qty8) > zeroifnull(sls_qty2) and
zeroifnull(sls_qty8) > zeroifnull(sls_qty3) and zeroifnull(sls_qty8)
> zeroifnull(sls_qty4) and zeroifnull(sls_qty8) >
zeroifnull(sls_qty5) and zeroifnull(sls_qty8) > zeroifnull(sls_qty6)
and zeroifnull(sls_qty8) > zeroifnull(sls_qty7) and
zeroifnull(sls_qty8) > zeroifnull(sls_qty1) and zeroifnull(sls_qty8)
>= zeroifnull(sls_qty9 ) and zeroifnull(sls_qty8) >=
zeroifnull(sls_qty10) and zeroifnull(sls_qty8) >= =
zeroifnull(sls_qty11)
and zeroifnull(sls_qty8) >= zeroifnull(sls_qty12) then -99999999 =
else
sls_qty8 end sls_qty8,
case when zeroifnull(sls_qty9) > zeroifnull(sls_qty2) and
zeroifnull(sls_qty9) > zeroifnull(sls_qty3) and zeroifnull(sls_qty9)
> zeroifnull(sls_qty4) and zeroifnull(sls_qty9) >
zeroifnull(sls_qty5) and zeroifnull(sls_qty9) > zeroifnull(sls_qty6)
and zeroifnull(sls_qty9) > zeroifnull(sls_qty7) and
zeroifnull(sls_qty9) > zeroifnull(sls_qty8) and zeroifnull(sls_qty9)
> zeroifnull(sls_qty1) and zeroifnull(sls_qty9) >=
zeroifnull(sls_qty10) and zeroifnull(sls_qty9) >= =
zeroifnull(sls_qty11)
and zeroifnull(sls_qty9) >= zeroifnull(sls_qty12) then -99999999 =
else
sls_qty9 end sls_qty9,
case when zeroifnull(sls_qty10) > zeroifnull(sls_qty2) and
zeroifnull(sls_qty10) > zeroifnull(sls_qty3) and zeroifnull(sls_qty10)
> zeroifnull(sls_qty4) and zeroifnull(sls_qty10) >
zeroifnull(sls_qty5) and zeroifnull(sls_qty10) > zeroifnull(sls_qty6)
and zeroifnull(sls_qty10) > zeroifnull(sls_qty7) and
zeroifnull(sls_qty10) > zeroifnull(sls_qty8) and zeroifnull(sls_qty10)
> zeroifnull(sls_qty9 ) and zeroifnull(sls_qty10) >
zeroifnull(sls_qty1) and zeroifnull(sls_qty10) >= =
zeroifnull(sls_qty11)
and zeroifnull(sls_qty10) >= zeroifnull(sls_qty12) then -99999999 =
else
sls_qty10 end sls_qty10,
case when zeroifnull(sls_qty11) > zeroifnull(sls_qty2) and
zeroifnull(sls_qty11) > zeroifnull(sls_qty3) and zeroifnull(sls_qty11)
> zeroifnull(sls_qty4) and zeroifnull(sls_qty11) >
zeroifnull(sls_qty5) and zeroifnull(sls_qty11) > zeroifnull(sls_qty6)
and zeroifnull(sls_qty11) > zeroifnull(sls_qty7) and
zeroifnull(sls_qty11) > zeroifnull(sls_qty8) and zeroifnull(sls_qty11)
> zeroifnull(sls_qty9 ) and zeroifnull(sls_qty11) >
zeroifnull(sls_qty10) and zeroifnull(sls_qty11) > zeroifnull(sls_qty1)
and zeroifnull(sls_qty11) >= zeroifnull(sls_qty12) then -99999999 =
else
sls_qty11 end sls_qty11,
case when zeroifnull(sls_qty12) > zeroifnull(sls_qty2) and
zeroifnull(sls_qty12) > zeroifnull(sls_qty3) and zeroifnull(sls_qty12)
> zeroifnull(sls_qty4) and zeroifnull(sls_qty12) >
zeroifnull(sls_qty5) and zeroifnull(sls_qty12) > zeroifnull(sls_qty6)
and zeroifnull(sls_qty12) > zeroifnull(sls_qty7) and
zeroifnull(sls_qty12) > zeroifnull(sls_qty8) and zeroifnull(sls_qty12)
> zeroifnull(sls_qty9 ) and zeroifnull(sls_qty12) >
zeroifnull(sls_qty10) and zeroifnull(sls_qty12) >
zeroifnull(sls_qty11) and zeroifnull(sls_qty12) > zeroifnull(sls_qty1)
then -99999999 else sls_qty12 end sls_qty12
from pbs_tables.pbs_data
where branch_id in (select branch_id from branch where group_id
in (8100,8500,8800) ) ) q ) qq) t
on t.branch_id = i.branch_id and t.sim_mfr_no = i.sim_mfr_no and
t.sim_item_no = i.sim_item_no
left outer join
(
sel branch_id, sim_mfr_no, sim_item_no, country_code,
case
when zeroifnull(max_lt ) > 0 and leadtime >= zeroifnull(max_lt ) =
then
max_lt
when leadtime < zeroifnull(max_lt ) then leadtime
when zeroifnull(max_lt) = 0 and leadtime = 0 then 1
when zeroifnull(max_lt) = 0 and leadtime > 0 then leadtime
when leadtime = 0 then 1
end leadtime_2
from
(
sel i.branch_id, i.sim_mfr_no, i.sim_item_no, i.country_code,
i.leadtime, max(h.leadtime) max_lt
from dly_inventory i left outer join inventory_history h
on i.branch_id = h.branch_id and i.sim_mfr_no = h.sim_mfr_no and
i.sim_item_no = h.sim_item_no and
h.date_loaded between '2002/10/01' and '2003/08/01' /* use last 11
months from history */
group by 1,2,3,4,5 ) q
)
infor on
i.branch_id = infor.branch_id and i.sim_mfr_no = infor.sim_mfr_no =
and
i.sim_item_no = infor.sim_item_no
where i.branch_id in (Select branch_id from dss_tables.branch where
group_id in (8100,8500,8800) )
| |
