David C
asked on
SQL Coding stuck in infinite loop
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
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.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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
lots of points to work on, thanks
ASKER
Thanks both, great help and tips
Open in new window
instead of:
Open in new window
?