nested foreach not working properly
I created a stored procedure that gives the average of payments (in
time or not for the last three checks) for a customer (due_date -
check_date) and it supposed to return the customer and the average.
However it returns nothing.
It returns something (the right value) if the nested foreach has the
variables (v_jrnl_num, v_jrnl_seq, v_org_code) replaced with values
instead.
I cannot understand what I am doing wrong. I appears though that
v_jrnl_num, v_jrnl_seq, v_org_code are not being read from the nested
foreach somehow.
Any suggestion?
Both procedures are shown below.
DOES NOT WORK
***************************************************************************************************
****************************************************************************************************
CREATE PROCEDURE av_payment(p_charge_to CHAR(10))
RETURNING CHAR(10), DECIMAL
DEFINE v_diff INTEGER;
DEFINE v_tot INTEGER;
DEFINE v_count INTEGER;
DEFINE v_iter1 INTEGER;
DEFINE v_average DECIMAL(12,2);
DEFINE v_jrnl_num INTEGER;
DEFINE v_jrnl_seq SMALLINT;
DEFINE v_org_code CHAR(2);
DEFINE v_cheque_date DATE;
LET v_diff = 0;
LET v_tot = 0;
LET v_count = 0;
LET v_iter1 = 0;
FOREACH cursor_1 FOR
select jrnl_num, jrnl_seq, org_code, cheque_date INTO v_jrnl_num,
v_jrnl_seq, v_org_code, v_cheque_date
from cash_j where charge_to = p_charge_to order by cheque_date desc
IF v_iter1 < 3 THEN
FOREACH cursor_2 FOR
select due_date - cheque_date into v_diff from cash_j a, cash_j_t b, ar_hist c
where a.org_code = b.org_code and a.jrnl_num = b.jrnl_num
and a.jrnl_seq = b.jrnl_seq and c.trans_seq = 1
and b.document = c.trans_num and b.trans_type = c.trans_type and
b.trans_type = 'DI'
and a.org_code = v_org_code
and a.jrnl_num = v_jrnl_num
and a.jrnl_seq = v_jrnl_seq
and a.charge_to = p_charge_to
LET v_tot = v_tot + v_diff;
LET v_count = v_count + 1;
END FOREACH
LET v_iter1 = v_iter1 + 1;
ELSE
EXIT FOREACH;
END IF
END FOREACH
IF v_count = 0 THEN
LET v_average = -999;
ELSE
LET v_average = v_tot/v_count;
END IF
RETURN p_charge_to, v_average;
END PROCEDURE;
********************************************************************************
IT DOES WORK
******************************************************************************
******************************************************************************
CREATE PROCEDURE av_payment(p_charge_to CHAR(10))
RETURNING CHAR(10), DECIMAL
DEFINE v_diff INTEGER;
DEFINE v_tot INTEGER;
DEFINE v_count INTEGER;
DEFINE v_iter1 INTEGER;
DEFINE v_average DECIMAL(12,2);
DEFINE v_jrnl_num INTEGER;
DEFINE v_jrnl_seq SMALLINT;
DEFINE v_org_code CHAR(2);
DEFINE v_cheque_date DATE;
LET v_diff = 0;
LET v_tot = 0;
LET v_count = 0;
LET v_iter1 = 0;
FOREACH cursor_1 FOR
select jrnl_num, jrnl_seq, org_code, cheque_date INTO v_jrnl_num,
v_jrnl_seq, v_org_code, v_cheque_date
from cash_j where charge_to = p_charge_to order by cheque_date desc
IF v_iter1 < 3 THEN
FOREACH cursor_2 FOR
select due_date - cheque_date into v_diff from cash_j a, cash_j_t b, ar_hist c
where a.org_code = b.org_code and a.jrnl_num = b.jrnl_num
and a.jrnl_seq = b.jrnl_seq and c.trans_seq = 1
and b.document = c.trans_num and b.trans_type = c.trans_type and
b.trans_type = 'DI'
and a.org_code = '01'
and a.jrnl_num = 3436
and a.jrnl_seq = 2
and a.charge_to = p_charge_to
LET v_tot = v_tot + v_diff;
LET v_count = v_count + 1;
END FOREACH
LET v_iter1 = v_iter1 + 1;
ELSE
EXIT FOREACH;
END IF
END FOREACH
IF v_count = 0 THEN
LET v_average = -999;
ELSE
LET v_average = v_tot/v_count;
END IF
RETURN p_charge_to, v_average;
END PROCEDURE;