Nested Loops

[Bonediggler1]

Hi-

I am trying to run nested loops, but it seems as though once the inner loop is done the whole thing terminates...rather than going back to the outer loop.

Below is the code:

WHILE @COUNTER > -13
BEGIN
SET @ACCTGID = AR_DASHBOARD.FN_ACCTPER(@ACCTGID1, @COUNTER)

WHILE @COUNTER2 < 3
BEGIN
INSERT INTO AR_DASHBOARD.HISTORICAL_DSO (PRODUCT, ACCTG_PERIOD_ID, TOTAL_AR, AR_OVER_90, SUM_REVENUE, DAYS)

SELECT (SELECT PRODUCT FROM #TEMP_PROD WHERE ROWNUM = @COUNTER2),
@ACCTGID,
(SELECT SUM(BAL) FROM AR_DASHBOARD.AR_OPEN WHERE GL_PRODUCT = (SELECT PRODUCT FROM #TEMP_PROD WHERE ROWNUM = @COUNTER2) AND ACCTG_PERIOD_ID = @ACCTGID),
(SELECT SUM(CASE WHEN DBO.FN_BUCKETMONTH(AGNG_DT, @ACCTGID) > '4' THEN BAL ELSE 0 END) FROM AR_DASHBOARD.AR_OPEN WHERE GL_PRODUCT = (SELECT PRODUCT FROM #TEMP_PROD WHERE ROWNUM = @COUNTER2) AND ACCTG_PERIOD_ID = @ACCTGID),
(SELECT SUM(TRNS_AMNT) FROM AR_DASHBOARD.AR_REVENUE WHERE GL_PRODUCT = (SELECT PRODUCT FROM #TEMP_PROD WHERE ROWNUM = @COUNTER2) AND ACCTG_PERIOD_ID = @ACCTGID),
(SELECT CASE WHEN RIGHT(@ACCTGID, 2) IN ('01','05','07','08','09','10', '12') THEN '92'
WHEN RIGHT(@ACCTGID, 2) IN ('02', '03') THEN '90'
WHEN RIGHT(@ACCTGID, 2) IN ('04') THEN '89'
WHEN RIGHT(@ACCTGID, 2) IN ('06', '11') THEN '91'
END)

SET @COUNTER2 = @COUNTER2 + 1
END

SET @COUNTER = @COUNTER -1
END


Thank you!!

 

[bborissov]

What is the initial value of @COUNTER?

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Bonediggler1]

Sorry...

SET @ACCTGID1 = 201009
SET @COUNTER = 0
SET @COUNTER2 = 1

 

[Bonediggler1]

BTW

I just removed the outer loop and the query returns the same results as with both loops. It's not registering the outer loop for some reason...

???

 

[Bonediggler1]

It seems as though the problem was not setting @COUNTER2 to 0 after completing the first outer loop. The following code works:


SET @ACCTGID1 = 201009
SET @COUNTER = 0
SET @COUNTER2 = 0


WHILE @COUNTER < 13
BEGIN
SET @COUNTER = @COUNTER + 1
SET @ACCTGID = AR_DASHBOARD.FN_ACCTPER(@ACCTGID1, -1*@COUNTER)

WHILE @COUNTER2 < 13
BEGIN
SET @COUNTER2 = @COUNTER2 + 1
INSERT INTO AR_DASHBOARD.HISTORICAL_DSO (PRODUCT, ACCTG_PERIOD_ID, TOTAL_AR, AR_OVER_90, SUM_REVENUE, DAYS)

SELECT (SELECT PRODUCT FROM #TEMP_PROD WHERE ROWNUM = @COUNTER2),
@ACCTGID,
(SELECT SUM(BAL) FROM AR_DASHBOARD.AR_OPEN WHERE GL_PRODUCT = (SELECT PRODUCT FROM #TEMP_PROD WHERE ROWNUM = @COUNTER2) AND ACCTG_PERIOD_ID = @ACCTGID),
(SELECT SUM(CASE WHEN DBO.FN_BUCKETMONTH(AGNG_DT, @ACCTGID) > '4' THEN BAL ELSE 0 END) FROM AR_DASHBOARD.AR_OPEN WHERE GL_PRODUCT = (SELECT PRODUCT FROM #TEMP_PROD WHERE ROWNUM = @COUNTER2) AND ACCTG_PERIOD_ID = @ACCTGID),
(SELECT SUM(TRNS_AMNT) FROM AR_DASHBOARD.AR_REVENUE WHERE GL_PRODUCT = (SELECT PRODUCT FROM #TEMP_PROD WHERE ROWNUM = @COUNTER2) AND ACCTG_PERIOD_ID = @ACCTGID),
(SELECT CASE WHEN RIGHT(@ACCTGID, 2) IN ('01','05','07','08','09','10', '12') THEN '92'
WHEN RIGHT(@ACCTGID, 2) IN ('02', '03') THEN '90'
WHEN RIGHT(@ACCTGID, 2) IN ('04') THEN '89'
WHEN RIGHT(@ACCTGID, 2) IN ('06', '11') THEN '91'
END)


END
SET @COUNTER2 = 0
END

 

[JoeAtWork]

It seems as though the problem was not setting @COUNTER2 to 0 after completing the first outer loop.

In your original code the outer loop actually looped 13 times, but the inner loop only worked in the first iteration of the outer loop. By the end of the first outer loop iteration, @Counter2 was set to a value that caused it to fail the test of the inner loop (i.e. it's value was 4). By setting it back to zero within the outer loop you allowed the inner loop to run in all iterations of the outer loop.