Hi, Adam

I did know now what exactly it causes the problem, if there is an GROUP BY clause involved in the statment, the 'for execute statement' would failed anyway. Otherwise everything worked just OK even if there is SUM(...) in the select clause. The following statment is executed OK since it doesn't have the GROUP BY:

sqlstring =' select sum(SHIPPING_CHARGE), min(INVOICE_NO), max(INVOICE_NO) ' ||
' from INVOICE ' ||
' where DIVISION_ID = ' || :divisionid || ' and ' ||
' POSTED = ''Y'' and INVOICE_NO > ' || :last_tariff_invoiceno;

for execute statement sqlstring
into :SHIPPING_CHARGE, :FROM_INVOICENO, :TO_INVOICENO
do
begin
suspend;
end


William

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

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

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]