Solved

MsSQL - While, Loop

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now