Hi,

There seems that it is a BUG in FireBird for sure. if there is no data can be return, everything just OK. but if there are data which match and needs to be returned. them BOOM, "Unsuccessful execution caused by an unavailable resource. variable type (position 3) in EXECUTE STATEMENT 'select m.DESCRIPTION, t.CODE, c.CODE, sum(id.QTY)," INTO does not match return." if you remark the execute statement, using the exactly same for select * instead, then NO error occurs.

William

Wei Yu <william_yuwei@...> wrote: Hi, Adam

Here is full correct table DDL script:

SET SQL DIALECT 3;

SET NAMES WIN1251;

SET CLIENTLIB 'fbclient.dll';

CREATE DATABASE 'C:\Optiq\DataBase\OPTIQ.FDB'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 8192
DEFAULT CHARACTER SET WIN1251;



/******************************************************************************/
/**** Domains ****/
/******************************************************************************/

CREATE DOMAIN BOOLEAN AS
CHAR(1)
NOT NULL
CHECK (VALUE IN ('Y', 'N'));



/******************************************************************************/
/**** Tables ****/
/******************************************************************************/



CREATE TABLE ACCESSORIES (
ID INTEGER NOT NULL,
DESCRIPTION VARCHAR(32),
RIVETED BOOLEAN DEFAULT 'N',
CODE VARCHAR(2),
INCLUDED BOOLEAN DEFAULT 'N'
);

CREATE TABLE CATEGORY (
ID INTEGER NOT NULL,
DESCRIPTION VARCHAR(128),
ISDEFAULT BOOLEAN DEFAULT 'N'
);

CREATE TABLE CLIENT_ADDRESS (
ADDRESSID INTEGER NOT NULL,
CLIENT_ID INTEGER,
CONTACT_ID INTEGER,
DEFAULT_ADDRESS BOOLEAN DEFAULT 'N'
);

CREATE TABLE CLIENT_COLLECTION (
ID INTEGER NOT NULL,
CLIENT_ID INTEGER NOT NULL,
COMMENT_DATE DATE,
COMMENT BLOB SUB_TYPE 1 SEGMENT SIZE 80,
FOLLOWUP_DATE DATE
);

CREATE TABLE CLIENT_DIVISION (
CLIENT_ID INTEGER NOT NULL,
DIVISION_ID INTEGER,
COMMISION DECIMAL(15,2),
INVOICE_DISCOUNT DECIMAL(15,2),
PAYMENT_DISCOUNT DECIMAL(15,2),
CLIENT_DISCOUNT DECIMAL(15,2),
PRICEGROUP_ID INTEGER,
BRAND_DISCOUNT NUMERIC(15,2)
);

CREATE TABLE CLIENT_GROUP (
ID INTEGER NOT NULL,
DESCRIPTION VARCHAR(64)
);

CREATE TABLE CLIENT_HISTORY (
CLIENT_ID INTEGER,
DIVISION_ID INTEGER,
PREVIOUSYEAR_SALES NUMERIC(15,2),
LASTYEAR_SALES NUMERIC(15,2)
);

CREATE TABLE CLIENT_PRICE (
ID INTEGER NOT NULL,
CLIENT_ID INTEGER NOT NULL,
INVENTORY_ID INTEGER,
PRICE DECIMAL(15,2)
);

CREATE TABLE CLIENTS (
ID INTEGER NOT NULL,
CONTACT_ID INTEGER,
IRS_NO VARCHAR(32),
CREDITSTATUS_ID INTEGER,
COMMENTS BLOB SUB_TYPE 1 SEGMENT SIZE 80,
FLAG_PURCHASEORDER_REQ BOOLEAN,
FLAG_SHIPTO_REQ BOOLEAN,
ALLOW_DAYDUE INTEGER DEFAULT 60 NOT NULL,
CREDIT_LIMIT NUMERIC(15,2) DEFAULT 0 NOT NULL,
CREDIT_CARD_1 VARCHAR(32),
CREDIT_CARD_2 VARCHAR(10),
ARRANGEMENTS BLOB SUB_TYPE 1 SEGMENT SIZE 80,
CLIENTGROUP_ID INTEGER,
OTHER_SHIPPING_ACCOUNT VARCHAR(32),
PREFER_COURIER VARCHAR(64),
FORCE_DIVISIONID INTEGER,
GROUPID VARCHAR(32)
);

CREATE TABLE COMMENT_TEMPLATE (
ID INTEGER NOT NULL,
SUBJECT VARCHAR(64),
COMMENTS BLOB SUB_TYPE 1 SEGMENT SIZE 80
);

CREATE TABLE CONTACTS (
ID INTEGER NOT NULL,
NAME VARCHAR(128),
ACCOUNT_NO VARCHAR(32),
STREET VARCHAR(128),
CITY VARCHAR(64),
POSTALCODE VARCHAR(32),
PROVINCE VARCHAR(64),
COUNTRY_ID INTEGER,
CONTACT VARCHAR(32),
PHONE VARCHAR(32),
FAX VARCHAR(32),
EMAIL VARCHAR(64),
DATE_STARTED DATE DEFAULT 'TODAY',
SALEREP_ID INTEGER,
CONTACT_TYPE CHAR(1) DEFAULT 'N',
PROXY_NAME VARCHAR(128),
PROXY_STREET VARCHAR(128),
PROXY_CITY VARCHAR(64),
ZONE VARCHAR(16)
);

CREATE TABLE COUNTRY (
ID INTEGER NOT NULL,
NAME VARCHAR(64),
TARIFF BOOLEAN,
CODE VARCHAR(8)
);

CREATE TABLE CREDITSTATUS (
ID INTEGER NOT NULL,
DESCRIPTION VARCHAR(128),
CREDIT_LEVEL INTEGER,
ONHOLD BOOLEAN DEFAULT 'N'
);

CREATE TABLE CURRENCIES (
ID INTEGER NOT NULL,
NAME VARCHAR(32),
EXCHANGE_RATE NUMERIC(15,4) DEFAULT 0 NOT NULL,
LAST_UPDATE DATE
);

CREATE TABLE DIVISION (
ID INTEGER NOT NULL,
NAME VARCHAR(64),
CODE VARCHAR(8),
LAST_TARIFF_INVOICENO INTEGER
);

CREATE TABLE DIVISION_ACCOUNT (
DIVISION_ID INTEGER,
COUNTRY_ID INTEGER,
ACCOUNT_NO VARCHAR(64),
ID INTEGER NOT NULL
);

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,
NETPRICING BOOLEAN DEFAULT 'N',
QTYONHAND INTEGER DEFAULT 0 NOT NULL,
QTYONHOLD INTEGER DEFAULT 0,
PRODUCTLINE_ID INTEGER,
QTYONORDER INTEGER DEFAULT 0 NOT NULL
);

CREATE TABLE INVENTORY_ACCESSORIES (
INVENTORY_ID INTEGER NOT NULL,
ACCESSORY_ID INTEGER NOT NULL,
INVENTORY_MODEL INTEGER
);

CREATE TABLE INVENTORY_BILLMATERIAL (
ID INTEGER NOT NULL,
INVENTORY_ID INTEGER,
ITEM_ID INTEGER,
QTY INTEGER,
RIVETED BOOLEAN DEFAULT 'N'
);

CREATE TABLE INVENTORY_MATRIX (
ID INTEGER NOT NULL,
INVENTORY_ID INTEGER,
ITEMCOLOR VARCHAR(32),
ITEMSIZE VARCHAR(32),
QTYONHAND INTEGER DEFAULT 0 NOT NULL,
UPC_A VARCHAR(16),
UPC_B VARCHAR(16),
UPC_C VARCHAR(16),
UPC_D VARCHAR(16),
UPC_E VARCHAR(16),
QTYONHOLD INTEGER DEFAULT 0 NOT NULL,
QTYONORDER INTEGER DEFAULT 0 NOT NULL
);

CREATE TABLE INVENTORY_PARTS (
INVENTORY_ID INTEGER,
PRICING_TYPE CHAR(1) DEFAULT 'U',
PARTS_ID INTEGER,
PRICE NUMERIC(15,2)
);

CREATE TABLE INVENTORY_SELLPRICING (
INVENTORY_ID INTEGER,
PRICEGROUP_ID INTEGER,
PRICE NUMERIC(15,2),
SPECIAL BOOLEAN DEFAULT 'N'
);

CREATE TABLE INVENTORY_TYPES (
CODE CHAR(1) NOT NULL,
DESCRIPTION VARCHAR(32)
);

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',
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,
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 PARTS (
ID INTEGER NOT NULL,
DESCRIPTION VARCHAR(128),
PRICING_TYPE CHAR(1) DEFAULT 'U',
DEFAULT_VALUE NUMERIC(15,2),
NETPRICING BOOLEAN DEFAULT 'N',
TARIFF_ID INTEGER,
COUNTRY_ID INTEGER
);

CREATE TABLE PAYMENT_DETAIL (
ID INTEGER NOT NULL,
INVOICE_ID INTEGER,
PAYMENT_DATE DATE DEFAULT 'TODAY',
AMOUNT NUMERIC(15,2) DEFAULT 0.00,
DISCOUNT NUMERIC(15,2) DEFAULT 0.00,
PAYMENTTYPE_ID INTEGER,
COMMENTS VARCHAR(128),
PAYTOACCOUNT_ID INTEGER,
REVERSE_ID INTEGER
);

CREATE TABLE PAYMENT_TYPE (
ID INTEGER NOT NULL,
NAME VARCHAR(64)
);

CREATE TABLE PRICEGROUP (
ID INTEGER NOT NULL,
DESCRIPTION VARCHAR(128)
);

CREATE TABLE PRODUCT_LINE (
ID INTEGER NOT NULL,
DESCRIPTION VARCHAR(64),
BRAND BOOLEAN DEFAULT 'N',
TRANSPORTATION NUMERIC(15,2) DEFAULT 0 NOT NULL,
DUTY NUMERIC(15,2) DEFAULT 0 NOT NULL,
PROXY_DESCRIPTION VARCHAR(64)
);

CREATE TABLE PURCHASE_ORDER (
ID INTEGER NOT NULL,
SUPPLIER_ID INTEGER,
PO_NO INTEGER,
ORDER_DATE DATE DEFAULT 'TODAY' NOT NULL,
POSTED BOOLEAN DEFAULT 'N' NOT NULL,
ENTRY_ID INTEGER,
EMAILED BOOLEAN DEFAULT 'N',
STATUS VARCHAR(1) DEFAULT 'O' NOT NULL
);

CREATE TABLE PURCHASEORDER_DETAIL (
ID INTEGER NOT NULL,
PO_ID INTEGER,
INVENTORY_ID INTEGER,
MATRIX_ID INTEGER,
QTY INTEGER,
ETD DATE,
SUPPLIER_ID INTEGER,
ENTRY_ID INTEGER
);

CREATE TABLE RECEIVINGS (
ID INTEGER NOT NULL,
PO_ID INTEGER,
MATRIX_ID INTEGER,
INVENTORY_ID INTEGER,
QTY INTEGER,
RECEIVED_DATE DATE DEFAULT 'TODAY'
);

CREATE TABLE RIVETING_DRILL (
COUNTRY_ID INTEGER NOT NULL,
PRICE NUMERIC(15,2) DEFAULT 0 NOT NULL
);

CREATE TABLE SALESREP (
ID INTEGER NOT NULL,
NAME VARCHAR(64)
);

CREATE TABLE SECURITY_GROUP (
ID INTEGER NOT NULL,
DESCRIPTION VARCHAR(32)
);

CREATE TABLE SECURITY_USER (
ID INTEGER NOT NULL,
USERNAME VARCHAR(32),
PASS_WORD VARCHAR(32),
GROUP_ID INTEGER,
INIT_DATE DATE,
SIGNIN_STAMP TIMESTAMP,
SIGNOFF_STAMP TIMESTAMP,
COMPUTER_NAME VARCHAR(128)
);

CREATE TABLE SETTINGS (
GST NUMERIC(15,2),
INVOICING_RETRY_COUNT INTEGER
);

CREATE TABLE SQL_MAPPINGS (
SQL_NAME VARCHAR(32) NOT NULL,
SQL_STATEMENT BLOB SUB_TYPE 1 SEGMENT SIZE 80
);

CREATE TABLE SUPPLIER (
ID INTEGER NOT NULL,
CONTACT_ID INTEGER,
COMMENTS BLOB SUB_TYPE 1 SEGMENT SIZE 80
);

CREATE TABLE TARIFF (
ID INTEGER NOT NULL,
DESCRIPTION VARCHAR(64),
CODE VARCHAR(32)
);

CREATE TABLE TEMP_TABLE (
CMD VARCHAR(2024)
);



/******************************************************************************/
/**** Primary Keys ****/
/******************************************************************************/

ALTER TABLE ACCESSORIES ADD CONSTRAINT PK_ACCESSORIES PRIMARY KEY (ID);
ALTER TABLE CATEGORY ADD CONSTRAINT PK_CATEGORY PRIMARY KEY (ID);
ALTER TABLE CLIENTS ADD CONSTRAINT PK_CLIENTS PRIMARY KEY (ID);
ALTER TABLE CLIENT_ADDRESS ADD CONSTRAINT PK_CLIENT_ADDRESS PRIMARY KEY (ADDRESSID);
ALTER TABLE CLIENT_COLLECTION ADD CONSTRAINT PK_CLIENT_COLLECTION PRIMARY KEY (ID);
ALTER TABLE CLIENT_GROUP ADD CONSTRAINT PK_CLIENT_GROUP PRIMARY KEY (ID);
ALTER TABLE CLIENT_PRICE ADD CONSTRAINT PK_CLIENT_PRICE PRIMARY KEY (ID);
ALTER TABLE COMMENT_TEMPLATE ADD CONSTRAINT PK_COMMENT_TEMPLATE PRIMARY KEY (ID);
ALTER TABLE CONTACTS ADD CONSTRAINT PK_CONTACTS PRIMARY KEY (ID);
ALTER TABLE COUNTRY ADD CONSTRAINT PK_COUNTRY PRIMARY KEY (ID);
ALTER TABLE CREDITSTATUS ADD CONSTRAINT PK_CREDITSTATUS PRIMARY KEY (ID);
ALTER TABLE CURRENCIES ADD CONSTRAINT PK_CURRENCIES PRIMARY KEY (ID);
ALTER TABLE DIVISION ADD CONSTRAINT PK_DIVISION PRIMARY KEY (ID);
ALTER TABLE DIVISION_ACCOUNT ADD CONSTRAINT PK_DIVISION_ACCOUNT PRIMARY KEY (ID);
ALTER TABLE INVENTORY ADD CONSTRAINT PK_INVENTORY PRIMARY KEY (ID);
ALTER TABLE INVENTORY_BILLMATERIAL ADD CONSTRAINT PK_INVENTORY_BILLMATERIAL PRIMARY KEY (ID);
ALTER TABLE INVENTORY_MATRIX ADD CONSTRAINT PK_INVENTORY_MATRIX PRIMARY KEY (ID);
ALTER TABLE INVENTORY_TYPES ADD CONSTRAINT PK_INVENTORY_TYPES PRIMARY KEY (CODE);
ALTER TABLE INVOICE ADD CONSTRAINT PK_INVOICE PRIMARY KEY (ID);
ALTER TABLE INVOICE_DETAIL ADD CONSTRAINT PK_INVOICE_DETAIL PRIMARY KEY (ID);
ALTER TABLE MATERIAL ADD CONSTRAINT PK_MATERIAL PRIMARY KEY (ID);
ALTER TABLE PARTS ADD CONSTRAINT PK_PARTS PRIMARY KEY (ID);
ALTER TABLE PAYMENT_DETAIL ADD CONSTRAINT PK_PAYMENT_DETAIL PRIMARY KEY (ID);
ALTER TABLE PAYMENT_TYPE ADD CONSTRAINT PK_PAYMENT_TYPE PRIMARY KEY (ID);
ALTER TABLE PRICEGROUP ADD CONSTRAINT PK_PRICEGROUP PRIMARY KEY (ID);
ALTER TABLE PRODUCT_LINE ADD CONSTRAINT PK_PRODUCT_LINE PRIMARY KEY (ID);
ALTER TABLE PURCHASEORDER_DETAIL ADD CONSTRAINT PK_PURCHASEORDER_DETAIL PRIMARY KEY (ID);
ALTER TABLE PURCHASE_ORDER ADD CONSTRAINT PK_PURCHASE_ORDER PRIMARY KEY (ID);
ALTER TABLE RECEIVINGS ADD CONSTRAINT PK_RECEIVINGS PRIMARY KEY (ID);
ALTER TABLE RIVETING_DRILL ADD CONSTRAINT PK_RIVETING_DRILL PRIMARY KEY (COUNTRY_ID);
ALTER TABLE SALESREP ADD CONSTRAINT PK_SALESREP PRIMARY KEY (ID);
ALTER TABLE SECURITY_GROUP ADD CONSTRAINT PK_SECURITY_GROUP PRIMARY KEY (ID);
ALTER TABLE SECURITY_USER ADD CONSTRAINT PK_SECURITY_USER PRIMARY KEY (ID);
ALTER TABLE SQL_MAPPINGS ADD CONSTRAINT PK_SQL_MAPPINGS PRIMARY KEY (SQL_NAME);
ALTER TABLE SUPPLIER ADD CONSTRAINT PK_SUPPLIER PRIMARY KEY (ID);
ALTER TABLE TARIFF ADD CONSTRAINT PK_TARIFF PRIMARY KEY (ID);


/******************************************************************************/
/**** Foreign Keys ****/
/******************************************************************************/

ALTER TABLE CLIENTS ADD CONSTRAINT FK_CLIENTS_1 FOREIGN KEY (CONTACT_ID) REFERENCES CONTACTS (ID) ON UPDATE CASCADE;
ALTER TABLE CLIENTS ADD CONSTRAINT FK_CLIENTS_2 FOREIGN KEY (CREDITSTATUS_ID) REFERENCES CREDITSTATUS (ID) ON UPDATE CASCADE;
ALTER TABLE CLIENTS ADD CONSTRAINT FK_CLIENTS_3 FOREIGN KEY (CLIENTGROUP_ID) REFERENCES CLIENT_GROUP (ID) ON UPDATE CASCADE;
ALTER TABLE CLIENTS ADD CONSTRAINT FK_CLIENTS_4 FOREIGN KEY (FORCE_DIVISIONID) REFERENCES DIVISION (ID) ON UPDATE CASCADE;
ALTER TABLE CLIENT_ADDRESS ADD CONSTRAINT FK_CLIENT_ADDRESS_1 FOREIGN KEY (CLIENT_ID) REFERENCES CLIENTS (ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE CLIENT_ADDRESS ADD CONSTRAINT FK_CLIENT_ADDRESS_2 FOREIGN KEY (CONTACT_ID) REFERENCES CONTACTS (ID) ON UPDATE CASCADE;
ALTER TABLE CLIENT_COLLECTION ADD CONSTRAINT FK_CLIENT_COLLECTION_1 FOREIGN KEY (CLIENT_ID) REFERENCES CLIENTS (ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE CLIENT_DIVISION ADD CONSTRAINT FK_CLIENT_DIVISION_1 FOREIGN KEY (CLIENT_ID) REFERENCES CLIENTS (ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE CLIENT_DIVISION ADD CONSTRAINT FK_CLIENT_DIVISION_2 FOREIGN KEY (DIVISION_ID) REFERENCES DIVISION (ID) ON UPDATE CASCADE;
ALTER TABLE CLIENT_DIVISION ADD CONSTRAINT FK_CLIENT_DIVISION_3 FOREIGN KEY (PRICEGROUP_ID) REFERENCES PRICEGROUP (ID) ON UPDATE CASCADE;
ALTER TABLE CLIENT_HISTORY ADD CONSTRAINT FK_CLIENT_HISTORY_1 FOREIGN KEY (CLIENT_ID) REFERENCES CLIENTS (ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE CLIENT_HISTORY ADD CONSTRAINT FK_CLIENT_HISTORY_2 FOREIGN KEY (DIVISION_ID) REFERENCES DIVISION (ID) ON UPDATE CASCADE;
ALTER TABLE CLIENT_PRICE ADD CONSTRAINT FK_CLIENT_PRICE_1 FOREIGN KEY (CLIENT_ID) REFERENCES CLIENTS (ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE CLIENT_PRICE ADD CONSTRAINT FK_CLIENT_PRICE_2 FOREIGN KEY (INVENTORY_ID) REFERENCES INVENTORY (ID) ON UPDATE CASCADE;
ALTER TABLE CONTACTS ADD CONSTRAINT FK_CONTACTS_1 FOREIGN KEY (COUNTRY_ID) REFERENCES COUNTRY (ID) ON UPDATE CASCADE;
ALTER TABLE CONTACTS ADD CONSTRAINT FK_CONTACTS_2 FOREIGN KEY (SALEREP_ID) REFERENCES SALESREP (ID) ON UPDATE CASCADE;
ALTER TABLE DIVISION_ACCOUNT ADD CONSTRAINT FK_DIVISION_ACCOUNT_1 FOREIGN KEY (COUNTRY_ID) REFERENCES COUNTRY (ID) ON UPDATE CASCADE
USING INDEX FK_DIVISION_COUNTRYID;
ALTER TABLE DIVISION_ACCOUNT ADD CONSTRAINT FK_DIVISION_ACCOUNT_2 FOREIGN KEY (DIVISION_ID) REFERENCES DIVISION (ID) ON DELETE CASCADE ON UPDATE CASCADE
USING INDEX FK_DIVISION_DIVISIONID;
ALTER TABLE INVENTORY ADD CONSTRAINT FK_INVENTORY_1 FOREIGN KEY (CATEGORY_ID) REFERENCES CATEGORY (ID) ON UPDATE CASCADE;
ALTER TABLE INVENTORY ADD CONSTRAINT FK_INVENTORY_2 FOREIGN KEY (DIVISION_ID) REFERENCES DIVISION (ID) ON UPDATE CASCADE;
ALTER TABLE INVENTORY ADD CONSTRAINT FK_INVENTORY_3 FOREIGN KEY (CURRENCY_ID) REFERENCES CURRENCIES (ID) ON UPDATE CASCADE;
ALTER TABLE INVENTORY ADD CONSTRAINT FK_INVENTORY_4 FOREIGN KEY (TARIFF_ID) REFERENCES TARIFF (ID) ON UPDATE CASCADE;
ALTER TABLE INVENTORY ADD CONSTRAINT FK_INVENTORY_5 FOREIGN KEY (COUNTRY_ID) REFERENCES COUNTRY (ID) ON UPDATE CASCADE;
ALTER TABLE INVENTORY ADD CONSTRAINT FK_INVENTORY_6 FOREIGN KEY (SUPPLIER_ID) REFERENCES SUPPLIER (ID) ON UPDATE CASCADE;
ALTER TABLE INVENTORY ADD CONSTRAINT FK_INVENTORY_7 FOREIGN KEY (MATERIAL_ID) REFERENCES MATERIAL (ID) ON UPDATE CASCADE;
ALTER TABLE INVENTORY ADD CONSTRAINT FK_INVENTORY_8 FOREIGN KEY (PRODUCTLINE_ID) REFERENCES PRODUCT_LINE (ID) ON UPDATE CASCADE;
ALTER TABLE INVENTORY_ACCESSORIES ADD CONSTRAINT FK_INVENTORY_ACCESSORIES_1 FOREIGN KEY (ACCESSORY_ID) REFERENCES ACCESSORIES (ID) ON UPDATE CASCADE;
ALTER TABLE INVENTORY_ACCESSORIES ADD CONSTRAINT FK_INVENTORY_ACCESSORIES_2 FOREIGN KEY (INVENTORY_MODEL) REFERENCES INVENTORY (ID) ON UPDATE CASCADE;
ALTER TABLE INVENTORY_ACCESSORIES ADD CONSTRAINT FK_INVENTORY_ACCESSORIES_3 FOREIGN KEY (INVENTORY_ID) REFERENCES INVENTORY (ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE INVENTORY_BILLMATERIAL ADD CONSTRAINT FK_INVENTORY_BILLMATERIAL_1 FOREIGN KEY (INVENTORY_ID) REFERENCES INVENTORY (ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE INVENTORY_BILLMATERIAL ADD CONSTRAINT FK_INVENTORY_BILLMATERIAL_2 FOREIGN KEY (ITEM_ID) REFERENCES INVENTORY (ID) ON UPDATE CASCADE;
ALTER TABLE INVENTORY_MATRIX ADD CONSTRAINT FK_INVENTORY_MATRIX_1 FOREIGN KEY (INVENTORY_ID) REFERENCES INVENTORY (ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE INVENTORY_PARTS ADD CONSTRAINT FK_INVENTORY_PARTS_1 FOREIGN KEY (INVENTORY_ID) REFERENCES INVENTORY (ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE INVENTORY_PARTS ADD CONSTRAINT FK_INVENTORY_PARTS_2 FOREIGN KEY (PARTS_ID) REFERENCES PARTS (ID) ON UPDATE CASCADE;
ALTER TABLE INVENTORY_SELLPRICING ADD CONSTRAINT FK_INVENTORY_SELLPRICING_1 FOREIGN KEY (INVENTORY_ID) REFERENCES INVENTORY (ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE INVENTORY_SELLPRICING ADD CONSTRAINT FK_INVENTORY_SELLPRICING_2 FOREIGN KEY (PRICEGROUP_ID) REFERENCES PRICEGROUP (ID) ON UPDATE CASCADE;
ALTER TABLE INVOICE ADD CONSTRAINT FK_INVOICE_1 FOREIGN KEY (CLIENT_ID) REFERENCES CLIENTS (ID) ON UPDATE CASCADE;
ALTER TABLE INVOICE ADD CONSTRAINT FK_INVOICE_2 FOREIGN KEY (DIVISION_ID) REFERENCES DIVISION (ID) ON UPDATE CASCADE;
ALTER TABLE INVOICE ADD CONSTRAINT FK_INVOICE_3 FOREIGN KEY (SHIPTO_ID) REFERENCES CONTACTS (ID) ON UPDATE CASCADE;
ALTER TABLE INVOICE ADD CONSTRAINT FK_INVOICE_4 FOREIGN KEY (SALESREP_ID) REFERENCES SALESREP (ID) ON UPDATE CASCADE;
ALTER TABLE INVOICE_DETAIL ADD CONSTRAINT FK_INVOICE_DETAIL_1 FOREIGN KEY (INVENTORY_ID) REFERENCES INVENTORY (ID) ON UPDATE CASCADE;
ALTER TABLE INVOICE_DETAIL ADD CONSTRAINT FK_INVOICE_DETAIL_2 FOREIGN KEY (MATRIX_ID) REFERENCES INVENTORY_MATRIX (ID) ON UPDATE CASCADE;
ALTER TABLE INVOICE_DETAIL ADD CONSTRAINT FK_INVOICE_DETAIL_3 FOREIGN KEY (PART_ID) REFERENCES PARTS (ID) ON UPDATE CASCADE;
ALTER TABLE INVOICE_DETAIL ADD CONSTRAINT FK_INVOICE_DETAIL_5 FOREIGN KEY (DIVISION_ID) REFERENCES DIVISION (ID) ON UPDATE CASCADE
USING INDEX FK_INVOICEDETAIL_DIVISIONID;
ALTER TABLE INVOICE_DETAIL ADD CONSTRAINT FK_INVOICE_DETAIL_6 FOREIGN KEY (INVOICE_ID) REFERENCES INVOICE (ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE MATERIAL ADD CONSTRAINT FK_MATERIAL_1 FOREIGN KEY (TARIFF_ID) REFERENCES TARIFF (ID) ON UPDATE CASCADE;
ALTER TABLE PARTS ADD CONSTRAINT FK_PARTS_1 FOREIGN KEY (TARIFF_ID) REFERENCES TARIFF (ID) ON UPDATE CASCADE;
ALTER TABLE PARTS ADD CONSTRAINT FK_PARTS_2 FOREIGN KEY (COUNTRY_ID) REFERENCES COUNTRY (ID) ON UPDATE CASCADE;
ALTER TABLE PAYMENT_DETAIL ADD CONSTRAINT FK_PAYMENT_DETAIL_1 FOREIGN KEY (INVOICE_ID) REFERENCES INVOICE (ID) ON UPDATE CASCADE;
ALTER TABLE PAYMENT_DETAIL ADD CONSTRAINT FK_PAYMENT_DETAIL_2 FOREIGN KEY (PAYMENTTYPE_ID) REFERENCES PAYMENT_TYPE (ID) ON UPDATE CASCADE;
ALTER TABLE PAYMENT_DETAIL ADD CONSTRAINT FK_PAYMENT_DETAIL_3 FOREIGN KEY (PAYTOACCOUNT_ID) REFERENCES DIVISION_ACCOUNT (ID) ON UPDATE CASCADE
USING INDEX FK_PAYTOACCOUNT_ID;
ALTER TABLE PURCHASEORDER_DETAIL ADD CONSTRAINT FK_PURCHASEORDER_DETAIL_1 FOREIGN KEY (INVENTORY_ID) REFERENCES INVENTORY (ID) ON UPDATE CASCADE;
ALTER TABLE PURCHASEORDER_DETAIL ADD CONSTRAINT FK_PURCHASEORDER_DETAIL_2 FOREIGN KEY (MATRIX_ID) REFERENCES INVENTORY_MATRIX (ID) ON UPDATE CASCADE;
ALTER TABLE PURCHASEORDER_DETAIL ADD CONSTRAINT FK_PURCHASEORDER_DETAIL_3 FOREIGN KEY (SUPPLIER_ID) REFERENCES SUPPLIER (ID) ON UPDATE CASCADE;
ALTER TABLE PURCHASE_ORDER ADD CONSTRAINT FK_PURCHASE_ORDER_1 FOREIGN KEY (SUPPLIER_ID) REFERENCES SUPPLIER (ID) ON UPDATE CASCADE;
ALTER TABLE RECEIVINGS ADD CONSTRAINT FK_RECEIVINGS_1 FOREIGN KEY (PO_ID) REFERENCES PURCHASE_ORDER (ID) ON UPDATE CASCADE;
ALTER TABLE RECEIVINGS ADD CONSTRAINT FK_RECEIVINGS_2 FOREIGN KEY (INVENTORY_ID) REFERENCES INVENTORY (ID) ON UPDATE CASCADE;
ALTER TABLE RECEIVINGS ADD CONSTRAINT FK_RECEIVINGS_3 FOREIGN KEY (MATRIX_ID) REFERENCES INVENTORY_MATRIX (ID) ON UPDATE CASCADE;
ALTER TABLE SECURITY_USER ADD CONSTRAINT FK_SECURITY_USER_1 FOREIGN KEY (GROUP_ID) REFERENCES SECURITY_GROUP (ID) ON UPDATE CASCADE;
ALTER TABLE SUPPLIER ADD CONSTRAINT FK_SUPPLIER_1 FOREIGN KEY (CONTACT_ID) REFERENCES CONTACTS (ID);


/******************************************************************************/
/**** Indices ****/
/******************************************************************************/

CREATE INDEX IDX_CONTACTS_ACCOUNTNO ON CONTACTS (ACCOUNT_NO);
CREATE INDEX IDX_CONTACTS_CITY ON CONTACTS (PROXY_CITY);
CREATE INDEX IDX_CONTACTS_NAME ON CONTACTS (PROXY_NAME);
CREATE INDEX IDX_CONTACTS_PHONE ON CONTACTS (PHONE);
CREATE INDEX IDX_CONTACTS_STREET ON CONTACTS (PROXY_STREET);
CREATE INDEX IDX_CONTACTS_TYPE ON CONTACTS (CONTACT_TYPE);
CREATE UNIQUE INDEX IDX_INVENTORY_MODELNO ON INVENTORY (MODEL_NO);
CREATE INDEX IDX_INVENTORY_TYPE ON INVENTORY (INVENTORY_TYPE);
CREATE INDEX IDX_MATRIX_UPCA ON INVENTORY_MATRIX (UPC_A);
CREATE INDEX IDX_MATRIX_UPCB ON INVENTORY_MATRIX (UPC_B);
CREATE INDEX IDX_MATRIX_UPCC ON INVENTORY_MATRIX (UPC_C);
CREATE INDEX IDX_MATRIX_UPCD ON INVENTORY_MATRIX (UPC_D);
CREATE INDEX IDX_MATRIX_UPCE ON INVENTORY_MATRIX (UPC_E);
CREATE INDEX IDX_INVOICE_INVOICEDATE ON INVOICE (INVOICE_DATE);
CREATE INDEX IDX_INVOICE_KIND ON INVOICE (KIND);
CREATE INDEX IDX_INVOICE_NO ON INVOICE (INVOICE_NO);
CREATE INDEX IDX_INVOICE_ORDERDATE ON INVOICE (ORDER_DATE);
CREATE INDEX IDX_INVOICE_POSTED ON INVOICE (POSTED);
CREATE INDEX IDX_INVOICE_REVERSEID ON INVOICE (REVERSEID);
CREATE INDEX IDX_INVOICEDETAIL_FRAMEID ON INVOICE_DETAIL (FRAME_ID);
CREATE INDEX IDX_INVOICEDETAIL_GROUPINDEX ON INVOICE_DETAIL (GROUPINDEX);
CREATE INDEX IDX_PAYMENTDETAIL_REVERSEID ON PAYMENT_DETAIL (REVERSE_ID);
CREATE INDEX PAYMENTDETAIL_DATE ON PAYMENT_DETAIL (PAYMENT_DATE);
CREATE INDEX IDX_PURCHASEORDER_POID ON PURCHASEORDER_DETAIL (PO_ID);
CREATE INDEX IDX_PURCHASEORDER_NO ON PURCHASE_ORDER (PO_NO);
CREATE INDEX IDX_PURCHASEORDER_POSTED ON PURCHASE_ORDER (POSTED);
CREATE INDEX IDX_PURCHASEORDER_STATUS ON PURCHASE_ORDER (STATUS);
CREATE INDEX SECURITY_USER_VALIDUSER ON SECURITY_USER (USERNAME, PASS_WORD);


Here is the stored procedure:

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,
FROM_INVOICENO INTEGER,
TO_INVOICENO INTEGER)
AS
DECLARE VARIABLE LAST_TARIFF_INVOICENO INTEGER;
DECLARE VARIABLE SQLSTRING VARCHAR(1024);
DECLARE VARIABLE EXCLUDEINVOICES VARCHAR(1024);
begin
excludeinvoices = '';

if (divisionid <= 0) then exit;

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), min(INVOICE_NO), max(INVOICE_NO)
from INVOICE
where DIVISION_ID = :divisionid and
POSTED = 'Y' and INVOICE_NO > :last_tariff_invoiceno
into :SHIPPING_CHARGE, :FROM_INVOICENO, :TO_INVOICENO;

DESCRIPTION = 'EYEGLASS FRAMES AND PARTS';
INVOICE_AMOUNT = 0;
SECTION = 0;
suspend;

/* MATARIAL SECTION, Invoices that have material set */

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

for 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
where i.DIVISION_ID = :divisionid and
i.POSTED = 'Y' and i.INVOICE_NO > :last_tariff_invoiceno
group by m.DESCRIPTION, t.CODE, c.CODE
into :DESCRIPTION, :TARIFF_CODE, :COUNTRY_CODE, :QTY, :INVOICE_AMOUNT
do
begin
suspend;
end

/* PARTS SECTION */

SECTION = 2;

for select 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 not null
inner join PARTS p on p.ID = id.PART_ID
inner join COUNTRY c on c.ID = p.COUNTRY_ID
inner join TARIFF t on t.ID = p.TARIFF_ID
where i.DIVISION_ID = :divisionid and
i.POSTED = 'Y' and i.INVOICE_NO > :last_tariff_invoiceno
group by t.CODE, c.CODE
into :TARIFF_CODE, :COUNTRY_CODE, :QTY, :INVOICE_AMOUNT
do
begin
DESCRIPTION = 'PARTS';
suspend;
end

DESCRIPTION = 'NON-PERSCRIPTION';
TARIFF_CODE = null;
COUNTRY_CODE = null;
QTY = null;
INVOICE_AMOUNT = 0;
SECTION = 3;
suspend;

/* INVENTORY SECTION, Invoices that have tariff code set but no material. */

SECTION = 4;

for select t.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
and iv.MATERIAL_ID is null
inner join COUNTRY c on c.ID = iv.COUNTRY_ID
inner join TARIFF t on t.ID = iv.TARIFF_ID
where i.DIVISION_ID = :divisionid and
i.POSTED = 'Y' and i.INVOICE_NO > :last_tariff_invoiceno
group by t.DESCRIPTION, t.CODE, c.CODE
into :DESCRIPTION, :TARIFF_CODE, :COUNTRY_CODE, :QTY, :INVOICE_AMOUNT
do
begin
suspend;
end

end;

Here is the test:

select * from report_tariffjournal(2)

Thanks

William

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

>
> Hi, Adam
>
> Yes. I'm using FB 1.5 higher, I'm thinking it could be a bug in

FB1.5, since I'm using the following generated SQL without problem,
but as long as I assign it to a string, and calling for execute
statement thisstring into then got the error.

I doubt this because I do not have the problem.

Your DDL you posted was incorrect (the domain would have to be
declared before the table and the procedure you execute in your test
is not defined).

I made some assumptions to try and get your problem on 1.5.2, and it
works fine for me.

Perhaps you can generate a SQL script (you must test it before
posting) that creates a database with this structure, then executes
the stored procedure. If you can get it to give you the error, then
post it back to the list and I will be able to duplicate it here.

Adam






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

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]