That may be true, but YOU need to provide a test case to prove this
bug. I have done as much as I can. Modify the script I sent you to
insert some data into whatever table is necessary to cause the "bug"
to show its head. I do not have time to generate test data in an
unfamiliar database guessing at the combinations that cause the
problem.

If you suspect there is a bug in Firebird, then you need to create a
simple script that you believe duplicates the bug. The script
provided by you so far does not duplicate the bug, but works fine. If
it turns out to be a bug that no-one else has reported, then we can
put it in the incident tracker, but you are not at that stage yet
because you do not know what causes it.

Adam



--- In firebird-support@yahoogroups.com, Wei Yu <william_yuwei@y...>
wrote:

>
> Hi, Adam
>
> I did investigate the problem for detail. It ends up that

everything went to OK, if you return a empty dataset. BUT got error
if there are data needs to be returned. and futher, if I comment the
SUM(..) fields and GROUP BY clause. then I get the result set from
this for execute statement. so IT MUST BE A BUG in the current
version of FireBird.

>
> William
>
>
> Adam <s3057043@y...> wrote: William,
>
> I will take a look at your DDL later (unless you resolve it before
> then). I have created from your previous scripts a version that

works

> inside my iSQL.
>
> I have ISC_USER and ISC_PASSWORD defined in my environment

variables

> so I do not need to provide them in iSQL.
>
> You don't seem to change your terminator character even though

your

> stored procedure contains ';' characters which would confuse iSQL

and

> alike, so I have added them.
>
> The final select does not return anything, but there is no error

here.

>
> Adam
>
> ----
>
> CREATE DATABASE "C:\MYTEST.FDB";
> COMMIT;
> CONNECT "C:\MYTEST.FDB";
>
>
> CREATE DOMAIN BOOLEAN AS
> CHAR(1) CHARACTER SET WIN1251
> NOT NULL
> CHECK (VALUE IN ('Y', 'N'))
> COLLATE WIN1251;
>
> CREATE TABLE INVOICE (
> ID INTEGER NOT NULL,
> CLIENT_ID INTEGER,
> DIVISION_ID INTEGER,
> INVOICE_NO INTEGER,
> INVOICE_DATE DATE,
> SHIPPING_CHARGE NUMERIC(15,2) DEFAULT 0 NOT NULL,
> DISCOUNT NUMERIC(15,2) DEFAULT 0 NOT NULL,
> SHIPTO_ID INTEGER,
> SALESREP_ID INTEGER,
> TRAY VARCHAR(128),
> POSTED BOOLEAN DEFAULT 'N' /* BOOLEAN = CHAR(1) NOT NULL CHECK
> (VALUE IN ('Y', 'N')) */,
> COMMENT VARCHAR(250),
> CREATE_DATE TIMESTAMP DEFAULT 'NOW',
> PICKEDBY VARCHAR(32),
> KIND VARCHAR(2) DEFAULT 'RO' NOT NULL,
> ORDER_NO INTEGER,
> ORDER_DATE DATE DEFAULT 'TODAY' NOT NULL,
> GROUPINDEX INTEGER,
> CREATORID INTEGER,
> POSTERID INTEGER,
> REVERSEID INTEGER,
> COMMISION NUMERIC(15,2) DEFAULT 0
> );
>
> CREATE TABLE INVOICE_DETAIL (
> ID INTEGER NOT NULL,
> INVOICE_ID INTEGER,
> INVENTORY_ID INTEGER,
> MATRIX_ID INTEGER,
> PART_ID INTEGER,
> QTY INTEGER DEFAULT 0 NOT NULL,
> UNIT_PRICE NUMERIC(15,2) DEFAULT 0 NOT NULL,
> RIVETING_PRICE NUMERIC(15,2) DEFAULT 0 NOT NULL,
> GROUPINDEX INTEGER,
> ADJUSTINVENTORY CHAR(1),
> COMMENTS VARCHAR(250),
> GST NUMERIC(15,2) DEFAULT 0 NOT NULL,
> POSTED CHAR(1) DEFAULT 'N' NOT NULL,
> NETPRICING BOOLEAN DEFAULT 'N' NOT NULL /* BOOLEAN = CHAR(1) NOT

NULL

> CHECK (VALUE IN ('Y', 'N')) */,
> BACKORDER_ID INTEGER,
> ETD DATE,
> INVENTORYTYPE CHAR(1) DEFAULT 'P' NOT NULL,
> DIVISION_ID INTEGER,
> FRAME_ID INTEGER,
> AMOUNT NUMERIC(15,4)
> );
>
> CREATE TABLE MATERIAL (
> ID INTEGER NOT NULL,
> DESCRIPTION VARCHAR(128),
> TARIFF_ID INTEGER
> );
>
> CREATE TABLE TARIFF (
> ID INTEGER NOT NULL,
> DESCRIPTION VARCHAR(64),
> CODE VARCHAR(32)
> );
>
> CREATE TABLE COUNTRY (
> ID INTEGER NOT NULL,
> NAME VARCHAR(64),
> TARIFF BOOLEAN /* BOOLEAN = CHAR(1) NOT NULL CHECK (VALUE IN
> ('Y', 'N'))
> */,
> CODE VARCHAR(8)
> );
>
> CREATE TABLE DIVISION (
> ID INTEGER NOT NULL,
> NAME VARCHAR(64),
> CODE VARCHAR(8),
> LAST_TARIFF_INVOICENO INTEGER
> );
>
> CREATE TABLE INVENTORY (
> ID INTEGER NOT NULL,
> MODEL_NO VARCHAR(64),
> DESCRIPTION VARCHAR(128),
> CATEGORY_ID INTEGER,
> DIVISION_ID INTEGER,
> INVENTORY_TYPE CHAR(1),
> CURRENCY_ID INTEGER,
> TARIFF_ID INTEGER,
> COUNTRY_ID INTEGER,
> SUPPLIER_ID INTEGER,
> COST_PRICE NUMERIC(15,2) DEFAULT 0,
> LANDED_COST NUMERIC(15,4) DEFAULT 0,
> START_DATE DATE DEFAULT 'TODAY',
> MATERIAL_ID INTEGER,
> GST_APPLICABLE BOOLEAN /* BOOLEAN = CHAR(1) NOT NULL CHECK (VALUE

IN

> ('Y',
> 'N')) */,
> NETPRICING BOOLEAN DEFAULT 'N' /* BOOLEAN = CHAR(1) NOT NULL CHECK
> (VALUE IN ('Y', 'N')) */,
> QTYONHAND INTEGER DEFAULT 0 NOT NULL,
> QTYONHOLD INTEGER DEFAULT 0,
> PRODUCTLINE_ID INTEGER,
> QTYONORDER INTEGER DEFAULT 0 NOT NULL
> );
>
> SET TERM ^ ;
>
> CREATE PROCEDURE REPORT_TARIFFJOURNAL (
> DIVISIONID INTEGER)
> RETURNS (
> DESCRIPTION VARCHAR(128),
> TARIFF_CODE VARCHAR(32),
> COUNTRY_CODE VARCHAR(8),
> QTY INTEGER,
> INVOICE_AMOUNT NUMERIC(15,4),
> SHIPPING_CHARGE NUMERIC(15,2),
> SECTION INTEGER)
> AS
> DECLARE VARIABLE LAST_TARIFF_INVOICENO INTEGER;
> DECLARE VARIABLE SQLSTRING VARCHAR(1024);
> DECLARE VARIABLE EXCLUDEINVOICES VARCHAR(1024);
> begin
> excludeinvoices = '';
>
> select LAST_TARIFF_INVOICENO from DIVISION
> where ID = :divisionid
> into :last_tariff_invoiceno;
>
> if (last_tariff_invoiceno is null) then
> last_tariff_invoiceno = 0;
>
> select sum(SHIPPING_CHARGE) from INVOICE
> where ((:divisionid <= 0) or (DIVISION_ID = :divisionid)) and
> POSTED = 'Y' and INVOICE_NO > :last_tariff_invoiceno
> into :SHIPPING_CHARGE;
>
> SECTION = 1;
>
> sqlstring = 'select m.DESCRIPTION, t.CODE, c.CODE, sum(id.QTY), sum
> (id.AMOUNT)
> '
> || 'from INVOICE i ' ||
> 'inner join INVOICE_DETAIL id on id.INVOICE_ID = i.ID and ' ||
> 'id.PART_ID is null and id.ADJUSTINVENTORY <> ''B'' '||
> 'inner join INVENTORY iv on iv.ID = id.INVENTORY_ID ' ||
> 'inner join COUNTRY c on c.ID = iv.COUNTRY_ID ' ||
> 'inner join MATERIAL m on m.ID = iv.MATERIAL_ID ' ||
> 'inner join TARIFF t on t.ID = m.TARIFF_ID ';
>
> if (divisionid > 0) then
> sqlstring = sqlstring ||
> 'where i.DIVISION_ID = ' || divisionid || ' and ' ||
> 'i.POSTED = ''Y'' and i.INVOICE_NO > ' || :last_tariff_invoiceno;
> else
> sqlstring = sqlstring ||
> 'where i.POSTED = ''Y'' and i.INVOICE_NO > '
> || :last_tariff_invoiceno;
>
> if (excludeinvoices <> '') then
> sqlstring = sqlstring ||
> ' and i.INVOICE_NO not in (' || excludeinvoices || ') ';
>
> sqlstring = sqlstring ||
> ' group by m.DESCRIPTION, t.CODE, c.CODE';
>
> for execute statement sqlstring
>

into :DESCRIPTION, :TARIFF_CODE, :COUNTRY_CODE, :QTY, :INVOICE_AMOUNT

> do
> begin
> suspend;
> end
>
> end
> ^
>
> SET TERM ;
> ^
>
> COMMIT;
>
> select * from
> report_tariffjournal(-1);
>
> -----
>
>
>
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> 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 Technical support
services

>
> ---------------------------------
> 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
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
> [Non-text portions of this message have been removed]
>