The select BBB ItemCode, BBB avaliableqty, BBB. U_MaterialAttr from (
The select t0 itemcode, cast (Round (sum (t1) onhand) - the sum (t1) iscommited), 0) as a decimal (18, 0)) as avaliableqty, t0. U_MaterialAttr
The from OITM t0
Left the join OITW t1 on t0. Itemcode=t1. The itemcode
Where t0. ValidFor='Y' and t1. Whscode='F1' and t0. U_MaterialAttr in (' A ', 'B', 'B', 'C +')
GROUP BY T0 Itemcode, T0. U_MaterialAttr) BBB where ((BBB) U_MaterialAttr in (' A ', 'B') and BBB. Avaliableqty<500) or (BBB. U_MaterialAttr in (' B ', 'C +') and BBB. Avaliableqty<200))
And BBB. ItemCode not in (select CCC. ItemCode COLLATE Chinese_PRC_CI_AS from [testing] [dbo] [AlertItemCode] as CCC where CCC. Docdate='2020-05-21')
CodePudding user response:
It ourselves, but also don't know which is better, or there are other betterThe select t0 itemcode, cast (Round (sum (t1) onhand) - the sum (t1) iscommited), 0) as a decimal (18, 0)) as avaliableqty, t0. U_MaterialAttr
The from OITM t0
Left the join OITW t1 on t0. Itemcode=t1. The itemcode
Where t0. ValidFor='Y' and t1. Whscode='F1' and t0. U_MaterialAttr in (' A ', 'B', 'B', 'C +')
GROUP BY T0 Itemcode, T0. U_MaterialAttr having ((T0) U_MaterialAttr in (' A ', 'B') and cast (Round (sum (t1) onhand) - the sum (t1) iscommited), 0) as A decimal (18, 0)) & lt; 500) or (t0. U_MaterialAttr in (' B ', 'C +') and cast (Round (sum (t1) onhand) - the sum (t1) iscommited), 0) as a decimal (18, 0)) & lt; 200))
And t0. ItemCode not in (select CCC. ItemCode COLLATE Chinese_PRC_CI_AS from [testing] [dbo] [AlertItemCode] as CCC where CCC. Docdate='2020-05-21')
CodePudding user response:
Look at the execution plan, see how many amount of data, to perform to try more time, and analyzing the specific problems toCodePudding user response:
Having nested and select which better?Not in (the) logically can replace with other?
CodePudding user response:
You this kind of SQL, not too much good optimization,First add index,
Or change a union all
The intermediate results are replaced with temporary table,