?
Solved

MsSQL - While, Loop

Posted on 2014-02-18
4
Medium Priority
?
666 Views
Last Modified: 2014-02-20
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
0
Comment
Question by:beckyng
  • 2
4 Comments
 
LVL 12

Expert Comment

by:Harish Varghese
ID: 39866806
Hello,

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
0
 

Author Comment

by:beckyng
ID: 39866809
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
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39868850
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
0
 
LVL 12

Accepted Solution

by:
Harish Varghese earned 1200 total points
ID: 39869629
Hello,

Examples of UPDATE using recursive CTE are available here:
http://msdn.microsoft.com/en-us/library/ms175972.aspx
But using an aggregate may be bit complex.

-Harish
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Viewers will learn how the fundamental information of how to create a table.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

592 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question