William,

What have you tried so far?

Have you checked it is not another condition getting in your way.
Start by extracting the query and running it in iSQL. You should get
the same problem. If so, try removing your having clause and the rest
of the where clause to see if this is the problem. If it is still at
this point, try and change your joins to left joins (temporarily) and
find out if you have a missing division or something like that which
will affect things.

Adam



--- In firebird-support@yahoogroups.com, "william_yuwei"
<william_yuwei@...> wrote:

>
> 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
>