Solved

MsSQL - While, Loop

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

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VBScript Write Column Headers 3 38
transaction in asp.net, sql server 6 33
SQL - Use results of SELECT DISTINCT in a JOIN 4 21
Sql Query 6 67
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

803 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