We help IT Professionals succeed at work.

SQL Coding stuck in infinite loop

75 Views
Last Modified: 2019-03-16
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
Comment
Watch Question

CERTIFIED EXPERT

Commented:
try:

SET @COUNTER = @COUNTER + 1
END

Open in new window


instead of:

END
SET @COUNTER = @COUNTER + 1

Open in new window


?
Senior Developer
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
David CIT Projects Manager

Author

Commented:
lots of points to work on, thanks
David CIT Projects Manager

Author

Commented:
Thanks both, great help and tips
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.