Re: nested foreach not working properly
Forget about this. It was a typo but there was no error returned though.
I guess it has been a very long day and I need a break.
Sorry guys,
On Tue, Mar 17, 2009 at 3:21 PM, Gentian Hila <genti.tech@gmail.com> wrote:
> 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;
>