troubleshooting Question

SQL Coding stuck in infinite loop

Avatar of David C
David CFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft SQL ServerSQL
4 Comments1 Solution85 ViewsLast Modified:
I seem to be struggling with the SQL code that I created and needs a few pointers on where I am going wrong. It seems to be caught in an infinite loop. I know it will be obvious once pointed out but its still a learning curve at the moment.

 DECLARE @TOTAL as int
DECLARE @CUSTOMER varchar(8)
DECLARE @PARTNO varchar(20)
DECLARE @CASE_DISCOUNT numeric(18, 2)
DECLARE @OVERRIDE_DISCOUNT numeric(18, 2)


DECLARE @COUNTER as int
SET @COUNTER = 1

SELECT        dbo.MBG110.CUSTOMER, dbo.MBG110.CUSTNAME, dbo.MBG130.SORDNO, dbo.MBG160.CINVOICE, dbo.MBG130.CUSTORDATE, dbo.MBG130.CUSTREQDTE_SOR, dbo.MBG160.CINVDATE,
                         dbo.MBG160.PARTNO_DS4, dbo.MBB010.DESCRIPTION, dbo.MBG160.DESQTY, dbo.MBB010.WEIGHT_ITM, dbo.MBG160.DESQTY * dbo.MBB010.WEIGHT_ITM AS ORDER_WEIGHT,
                         dbo.MBG130.SIINVVAL_SOR
                                     INTO #TempSales
FROM            dbo.MBG110 RIGHT OUTER JOIN
                         dbo.MBG130 ON dbo.MBG110.CUSTOMERLONG = dbo.MBG130.CUSTOMER_SOR LEFT OUTER JOIN
                         dbo.MBG160 ON dbo.MBG130.SORDNO = dbo.MBG160.SORDNO_DES LEFT OUTER JOIN
                         dbo.MBB010 ON dbo.MBG160.PARTNO_DS4 = dbo.MBB010.PARTNO

ALTER TABLE #TempSales ADD Case_Discount numeric(18, 2)
ALTER TABLE #TempSales ADD Override_Discount numeric(18, 2)
ALTER TABLE #TempSales ADD RowId int identity(1,1)

SELECT @TOTAL = COUNT(CUSTOMER) FROM #TempSales

SELECT        dbo.MBN510.PROMOCODE_N51, dbo.MBN510.CUSTOMER_N51, dbo.MBN510.EFFDATE_N51, dbo.MBN510.TERMDATE_N51, dbo.MBN520.PARTNO_N52, dbo.MBN520.UOM_REB,
                         dbo.MBN520.REBVALPERC
                                     INTO #TempDiscounts
FROM            dbo.MBN510 LEFT OUTER JOIN
                         dbo.MBN520 ON dbo.MBN510.PROMOCODE_N51 = dbo.MBN520.PROMOCODE_N52

WHILE(@COUNTER <= @TOTAL)
BEGIN

SELECT @CUSTOMER = CUSTOMER, @PARTNO = PARTNO_DS4 FROM #TempSales WHERE RowId = @COUNTER

SELECT @CASE_DISCOUNT = REBVALPERC FROM #TempDiscounts WHERE CUSTOMER_N51 = @CUSTOMER AND PARTNO_N52 = @PARTNO AND UOM_REB = 'CASE'

SELECT @OVERRIDE_DISCOUNT = REBVALPERC FROM #TempDiscounts WHERE CUSTOMER_N51 = @CUSTOMER AND PARTNO_N52 = @PARTNO AND UOM_REB <> 'CASE'
UPDATE #TempSales SET Case_Discount = @CASE_DISCOUNT
UPDATE #TempSales SET Override_Discount = @OVERRIDE_DISCOUNT
END
SET @COUNTER = @COUNTER + 1

sqlError.jpg
ASKER CERTIFIED SOLUTION
ste5an
Senior Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros