Solved

MsSQL - While, Loop

Posted on 2014-02-18
4
616 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 300 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

821 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