HI,

What I did try is that moving the checking for from account and to account to the begin .. end block. everything worked just fine. I don't think my query did thing wrong.

William

Adam <s3057043@...> wrote: 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
>

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++




SPONSORED LINKS
Technical support Computer technical support Compaq computer technical support Compaq technical support Hewlett packard technical support Microsoft technical support

---------------------------------
YAHOO! GROUPS LINKS


Visit your group "firebird-support" on the web.

To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


---------------------------------





William, Yu


---------------------------------
Brings words and photos together (easily) with
PhotoMail - it's free and works with Yahoo! Mail.

[Non-text portions of this message have been removed]