SQL Coding stuck in infinite loop

David C
David C used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

Commented:
try:

SET @COUNTER = @COUNTER + 1
END

Open in new window


instead of:

END
SET @COUNTER = @COUNTER + 1

Open in new window


?
Senior Developer
Commented:
Some comments:
- Use a cursor instead of your loop.
- Use a primary key instead of your RowID construct.
- The SELECT statements for @CASE_DISCOUNT and @OVERRIDE_DISCOUNT can return abritrary values, if you don't use a primary/candidate key in your WHERE condition and without TOP (1) / ORDER BY.

Finally:
You update always the entire sales temporary table. Thus you don't need a loop or cursor at all.

But when this is no correct, then you can also calculate all the necessary values @CASE_DISCOUNT and @OVERRIDE_DISCOUNT when creating your temporary discount table. Thus you should be able to use single UPDATE using both temporary tables instead of a loop/cursor.
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial