Hi, All

I've got a very strange problem, I have a sql statement in a stored
procedure which works OK UNTIL TODAY. It just suddenly return strange
result. I have a field called ACCOUNT_NO is of type INTEGER, in the
where clause I have like, ACCOUNT_NO between :FromAccount and
:ToAccount, if FromAcccount is 1 and ToAccount is 1000, it should
return whatever the range in 1-1000. but all suddenly it returns 1,
10, 100, 1000, total 4 records always no matter how many records it
should have.

The following is the statement I used which working just fine until now.

CREATE PROCEDURE ACCOUNTS_RECEIVABLE (
DIVISIONID INTEGER,
FROM_ACCOUNT INTEGER,
TO_ACCOUNT INTEGER,
CLIENTGROUPID INTEGER,
AS_OF_DATE DATE,
OVERDUE_DAYS INTEGER,
SALESREP_ID INTEGER)
RETURNS (
DIVISION_ID INTEGER,
DIVISION_NAME VARCHAR(64),
CLIENT_ID INTEGER,
CLIENTGROUP_ID INTEGER,
ACCOUNT_NO INTEGER,
CLIENT_NAME VARCHAR(128),
SALESREP VARCHAR(64),
BALANCE_TOTAL NUMERIC(15,2),
BALANCE_30 NUMERIC(15,2),
BALANCE_60 NUMERIC(15,2),
BALANCE_90 NUMERIC(15,2),
BALANCE_90OVER NUMERIC(15,2))
AS
DECLARE VARIABLE DAYS INTEGER;
DECLARE VARIABLE INVOICE_DATE DATE;
DECLARE VARIABLE INVOICE_BALANCE NUMERIC(15,2);
begin
for select i.DIVISION_ID, d.NAME, c.ID, a.CLIENTGROUP_ID, a.ACCOUNT_NO,
a.NAME, s.NAME, i.INVOICE_DATE, sum(i.BALANCE)
from CONTACTS a
inner join CLIENTS c on c.CONTACT_ID = a.ID
inner join INVOICE i on i.CLIENT_ID = c.ID and
i.INVOICE_DATE <= :as_of_date and
(:divisionid <= 0 or i.DIVISION_ID = :divisionid) and
(:salesrep_id <= 0 or i.SALESREP_ID = :salesrep_id)
inner join DIVISION d on d.ID = i.DIVISION_ID
left join SALESREP s on s.ID = i.SALESREP_ID
where (a.ACCOUNT_NO between :from_account and :to_account) and
(:clientgroupid <= 0 or a.CLIENTGROUP_ID = :clientgroupid)
group by i.DIVISION_ID, d.NAME, c.ID, a.CLIENTGROUP_ID,
a.ACCOUNT_NO,
a.NAME, s.NAME, i.INVOICE_DATE
having sum(i.BALANCE) <> 0
into :DIVISION_ID, :DIVISION_NAME, :CLIENT_ID, :CLIENTGROUP_ID,
:ACCOUNT_NO,
:CLIENT_NAME, :SALESREP, :invoice_date, :invoice_balance
do
begin
BALANCE_30 = 0;
BALANCE_60 = 0;
BALANCE_90 = 0;
BALANCE_90OVER = 0;

if (CLIENTGROUP_ID is null) then CLIENTGROUP_ID = 0;

days = CURRENT_DATE - invoice_date;

if (days >= overdue_days) then
begin
if (days <= 30) then
BALANCE_30 = invoice_balance;
else if (days <= 60) then
BALANCE_60 = invoice_balance;
else if (days <= 90) then
BALANCE_90 = invoice_balance;
else
BALANCE_90OVER = invoice_balance;

BALANCE_TOTAL = BALANCE_30 + BALANCE_60 + BALANCE_90
+ BALANCE_90OVER;

suspend;
end
end


In order to make it work again I have to change to the following code,
NOTE, checking FromAccount and ToAccount range, now is moved to the
begin ... end block.

CREATE PROCEDURE ACCOUNTS_RECEIVABLE (
DIVISIONID INTEGER,
FROM_ACCOUNT INTEGER,
TO_ACCOUNT INTEGER,
CLIENTGROUPID INTEGER,
AS_OF_DATE DATE,
OVERDUE_DAYS INTEGER,
SALESREP_ID INTEGER)
RETURNS (
DIVISION_ID INTEGER,
DIVISION_NAME VARCHAR(64),
CLIENT_ID INTEGER,
CLIENTGROUP_ID INTEGER,
ACCOUNT_NO INTEGER,
CLIENT_NAME VARCHAR(128),
SALESREP VARCHAR(64),
BALANCE_TOTAL NUMERIC(15,2),
BALANCE_30 NUMERIC(15,2),
BALANCE_60 NUMERIC(15,2),
BALANCE_90 NUMERIC(15,2),
BALANCE_90OVER NUMERIC(15,2))
AS
DECLARE VARIABLE DAYS INTEGER;
DECLARE VARIABLE INVOICE_DATE DATE;
DECLARE VARIABLE INVOICE_BALANCE NUMERIC(15,2);
begin
for select i.DIVISION_ID, d.NAME, c.ID, a.CLIENTGROUP_ID, a.ACCOUNT_NO,
a.NAME, s.NAME, i.INVOICE_DATE, sum(i.BALANCE)
from CONTACTS a
inner join CLIENTS c on c.CONTACT_ID = a.ID
inner join INVOICE i on i.CLIENT_ID = c.ID and
i.INVOICE_DATE <= :as_of_date and
(:divisionid <= 0 or i.DIVISION_ID = :divisionid) and
(:salesrep_id <= 0 or i.SALESREP_ID = :salesrep_id)
inner join DIVISION d on d.ID = i.DIVISION_ID
left join SALESREP s on s.ID = i.SALESREP_ID
where
(:clientgroupid <= 0 or a.CLIENTGROUP_ID = :clientgroupid)
group by i.DIVISION_ID, d.NAME, c.ID, a.CLIENTGROUP_ID,
a.ACCOUNT_NO,
a.NAME, s.NAME, i.INVOICE_DATE
having sum(i.BALANCE) <> 0
into :DIVISION_ID, :DIVISION_NAME, :CLIENT_ID, :CLIENTGROUP_ID,
:ACCOUNT_NO,
:CLIENT_NAME, :SALESREP, :invoice_date, :invoice_balance
do
begin
BALANCE_30 = 0;
BALANCE_60 = 0;
BALANCE_90 = 0;
BALANCE_90OVER = 0;

if (CLIENTGROUP_ID is null) then CLIENTGROUP_ID = 0;

days = CURRENT_DATE - invoice_date;

if (days >= overdue_days and
ACCOUNT_NO >= from_account and ACCOUNT_NO <= to_account) then
begin
if (days <= 30) then
BALANCE_30 = invoice_balance;
else if (days <= 60) then
BALANCE_60 = invoice_balance;
else if (days <= 90) then
BALANCE_90 = invoice_balance;
else
BALANCE_90OVER = invoice_balance;

BALANCE_TOTAL = BALANCE_30 + BALANCE_60 + BALANCE_90
+ BALANCE_90OVER;

suspend;
end
end
end

Can anybody help me to figured out what's going on?

Thanks

William