beckyng
asked on
MsSQL - While, Loop
Hi all Experts
I am writing a statement as belows to update the cost from downside to topside by level.
Actually, I dont want to do "Update" statement for 8 times (from Level 8 to Level 1).
Is there any way to modify the statement instead of 8 times of update action?
Declare @intFlag INT
set @intFlag = 8
while @intFlag <= 8
Begin
Update q
set q.PMaterialCost = a.MC * q.NewUsage
from dbo.tbl_MBMBOM q
inner join
(select Product,Facility,Parent, sum(MaterialCost) as MC
from dbo.tbl_MBMBOM where period = 201401 and facility = 'AA' and [Level] = @intFlag + 1
group by Parent,Facility,Product
) as a
on q.child = a.Parent and q.Flag = a.Facility and q.Product = a.Product
where q.Period = 201401 and q.Facility = 'AA' and [Level] = @intFlag
SET @intFlag = @intFlag - 1
CONTINUE;
IF @intFlag = 1
BREAK;
END
I am writing a statement as belows to update the cost from downside to topside by level.
Actually, I dont want to do "Update" statement for 8 times (from Level 8 to Level 1).
Is there any way to modify the statement instead of 8 times of update action?
Declare @intFlag INT
set @intFlag = 8
while @intFlag <= 8
Begin
Update q
set q.PMaterialCost = a.MC * q.NewUsage
from dbo.tbl_MBMBOM q
inner join
(select Product,Facility,Parent, sum(MaterialCost) as MC
from dbo.tbl_MBMBOM where period = 201401 and facility = 'AA' and [Level] = @intFlag + 1
group by Parent,Facility,Product
) as a
on q.child = a.Parent and q.Flag = a.Facility and q.Product = a.Product
where q.Period = 201401 and q.Facility = 'AA' and [Level] = @intFlag
SET @intFlag = @intFlag - 1
CONTINUE;
IF @intFlag = 1
BREAK;
END
ASKER
Hi Harish
Thanks for reply.
Please kindly assume my update statement is fully correct. Actually, I hope I can eliminate the execute the statement for 8 times on intFlag.
How can I do?
Thanks
Becky
Thanks for reply.
Please kindly assume my update statement is fully correct. Actually, I hope I can eliminate the execute the statement for 8 times on intFlag.
How can I do?
Thanks
Becky
Hi Becky,
You have a hierarchy.
There are examples of using cte's to do the selects from these, but I haven't seen an update.
http://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx
Can you post structure of dbo.tbl_MBMBOM and some sample data?
Regards
David
You have a hierarchy.
There are examples of using cte's to do the selects from these, but I haven't seen an update.
http://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx
Can you post structure of dbo.tbl_MBMBOM and some sample data?
Regards
David
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I am not sure about a way to avoid loop here, but wanted to point out some potential issues in your query:
1. If your highest available level is 8, then you may initialize @intFlag = 7, since you are using @intFlag + 1 in your subquery.
2. WHERE condition shows q.Flag = a.Facility; do you mean q.Facility = a.Facility instead?
3. CONTINUE statement skips all subsequent statements till the end of the loop, so it should not be there. In fact, you can remove the CONTINUE and IF @intFlag = 1 BREAK statements, and replace "while @intFlag <= 8" with "while @intFlag >= 1"
By the way, I think this is a complex question and 300 points is too less for this.
-Harish