Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MsSQL - While, Loop

Posted on 2014-02-18
4
Medium Priority
?
640 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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.

688 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