Re: Error code 42X05. Table doesn't exist.Pls help with code.
On 6/28/11 4:41 AM, IkeAbalogu wrote: Since you mentioned stored procedure as an alternative i have been trying to find a tutorial that will guide me to writing a stored procedure but have not found any.I find the reference manual article on it confusing. pls help if you have any websites i can look at. I responded to the "stored procedure tutorial help" thread. Hope that helps... -Rick Thanks again. Rick Hillegas-3 wrote: Instead of declaring a trigger which fires an update statement, you might want to consider declaring a trigger which fires a database procedure--the procedure would then update the row which you just inserted. The resulting code might be easier to reason about. Hope this helps, -Rick On 6/27/11 8:16 AM, IkeAbalogu wrote: Thanks.I see the error.I am updating newrow because it is the newly inserted row that the trigger will update its previousbalance column.I will try to come with a where statement to get to the newly inserted row.Your help will still be appreciated. Thanks again. Rick Hillegas-3 wrote: After running the database creation script, I see the reported 42X05 error when creating the problem trigger: CREATE TRIGGER NEWBALANCE AFTER INSERT ON APP.PRODUCTQUANTMONITOR REFERENCING NEW AS NEWROW FOR EACH ROW UPDATE NEWROW SET PREVIOUS_BALANCE = CASE WHEN (SELECT COUNT (NEWROW.FK1_SUPPLYID) FROM APP.PRODUCTQUANTMONITOR) = 1 THEN (SELECT QUANTITY FROM APP.PRODUCTSUPPLY WHERE SUPPLYID = NEWROW.FK1_SUPPLYID) ELSE (SELECT MIN(DISTINCT BALANCE) FROM PRODUCTQUANTMONITOR WHERE FK1_SUPPLYID = NEWROW.FK1_SUPPLYID AND TANKNUMBER = NEWROW.TANKNUMBER) END ; ERROR 42X05: Table/View 'NEWROW' does not exist. The error occurs because the triggered statement is trying to update the transient transition variable NEWROW rather than an actual table. The following rewritten trigger definition does compile and run cleanly. I don't know if this is what you are trying to achieve, though: CREATE TRIGGER NEWBALANCE AFTER INSERT ON APP.PRODUCTQUANTMONITOR REFERENCING NEW AS NEWROW FOR EACH ROW UPDATE APP.PRODUCTQUANTMONITOR SET PREVIOUS_BALANCE = CASE WHEN (SELECT COUNT (FK1_SUPPLYID) FROM APP.PRODUCTQUANTMONITOR) = 1 THEN (SELECT QUANTITY FROM APP.PRODUCTSUPPLY WHERE SUPPLYID = NEWROW.FK1_SUPPLYID) ELSE (SELECT MIN(DISTINCT BALANCE) FROM PRODUCTQUANTMONITOR WHERE FK1_SUPPLYID = NEWROW.FK1_SUPPLYID AND TANKNUMBER = NEWROW.TANKNUMBER) END ; Hope this helps, -Rick On 6/27/11 6:55 AM, IkeAbalogu wrote: Thanks.For your interest and help.Below is the entire schema -- -- Database creation Script -- -- Create a Database table to represent the "product" entity. CREATE TABLE product( nameVARCHAR(30) NOT NULL, description LONG VARCHAR NOT NULL, source LONG VARCHAR NOT NULL, -- Specify the PRIMARY KEY constraint for table "product". -- This indicates which attribute(s) uniquely identify each row of data. CONSTRAINT pk_product PRIMARY KEY (name) ); -- Create a Database table to represent the "productsupply" entity. CREATE TABLE productsupply( supplyidINTEGER NOT NULL, supply_date DATE NOT NULL, isfinished SMALLINT NOT NULL, finish_date DATE NOT NULL, quantityBIGINT NOT NULL, costprice DOUBLE PRECISION NOT NULL, saleprice DOUBLE PRECISION NOT NULL, fk1_nameVARCHAR(30) NOT NULL, -- Specify the PRIMARY KEY constraint for table "productsupply". -- This indicates which attribute(s) uniquely identify each row of data. CONSTRAINT pk_productsupply PRIMARY KEY (supplyid) ); -- Create a Database table to represent the "productquantmonitor" entity. CREATE TABLE productquantmonitor( checkdate DATE NOT NULL, tanknumber INTEGER NOT NULL, balance BIGINT NOT NULL, previous_balanceBIGINT NOT NULL, quantitysoldBIGINT NOT NULL, fk1_supplyidINTEGER NOT NULL ); -- Create a Database table to represent the "producttank" entity. CREATE TABLE producttank( tankid INTEGER NOT NULL, tankcapacityBIGINT NOT NULL, fk1_nameVARCHAR(30) NOT NULL, -- Specify the PRIMARY KEY constraint for table "producttank". -- This indicates which attribute(s) uniquely identify each row of data. CONSTRAINT pk_producttank PRIMARY KEY (tankid) ); -- -- Alter Tables to add fk constraints -- -- Now all the tables have been created the ALTER TABLE command is used to define some additional -- constraints. These typically constrain values of foreign keys to be associated in some way -- with the primary keys of related tables. Foreign key constraints can actually be specified -- when each table is created, but doing so can lead to dep
Re: Error code 42X05. Table doesn't exist.Pls help with code.
Since you mentioned stored procedure as an alternative i have been trying to find a tutorial that will guide me to writing a stored procedure but have not found any.I find the reference manual article on it confusing. pls help if you have any websites i can look at. Thanks again. Rick Hillegas-3 wrote: > > Instead of declaring a trigger which fires an update statement, you > might want to consider declaring a trigger which fires a database > procedure--the procedure would then update the row which you just > inserted. The resulting code might be easier to reason about. > > Hope this helps, > -Rick > > On 6/27/11 8:16 AM, IkeAbalogu wrote: >> Thanks.I see the error.I am updating newrow because it is the newly >> inserted >> row that the trigger will update its previousbalance column.I will try to >> come with a where statement to get to the newly inserted row.Your help >> will >> still be appreciated. >> Thanks again. >> >> >> >> Rick Hillegas-3 wrote: >>> After running the database creation script, I see the reported 42X05 >>> error when creating the problem trigger: >>> >>> CREATE TRIGGER NEWBALANCE >>> AFTER INSERT ON APP.PRODUCTQUANTMONITOR >>> REFERENCING NEW AS NEWROW >>> FOR EACH ROW >>> UPDATE NEWROW SET PREVIOUS_BALANCE = >>> CASE >>> WHEN (SELECT COUNT (NEWROW.FK1_SUPPLYID) FROM APP.PRODUCTQUANTMONITOR) = >>> 1 >>> THEN (SELECT QUANTITY FROM APP.PRODUCTSUPPLY WHERE SUPPLYID = >>> NEWROW.FK1_SUPPLYID) >>> ELSE (SELECT MIN(DISTINCT BALANCE) FROM PRODUCTQUANTMONITOR WHERE >>> FK1_SUPPLYID = NEWROW.FK1_SUPPLYID >>> AND TANKNUMBER = NEWROW.TANKNUMBER) >>> END >>> ; >>> ERROR 42X05: Table/View 'NEWROW' does not exist. >>> >>> The error occurs because the triggered statement is trying to update the >>> transient transition variable NEWROW rather than an actual table. The >>> following rewritten trigger definition does compile and run cleanly. I >>> don't know if this is what you are trying to achieve, though: >>> >>> CREATE TRIGGER NEWBALANCE >>> AFTER INSERT ON APP.PRODUCTQUANTMONITOR >>> REFERENCING NEW AS NEWROW >>> FOR EACH ROW >>> UPDATE APP.PRODUCTQUANTMONITOR SET PREVIOUS_BALANCE = >>> CASE >>> WHEN (SELECT COUNT (FK1_SUPPLYID) FROM APP.PRODUCTQUANTMONITOR) = 1 >>> THEN (SELECT QUANTITY FROM APP.PRODUCTSUPPLY WHERE SUPPLYID = >>> NEWROW.FK1_SUPPLYID) >>> ELSE (SELECT MIN(DISTINCT BALANCE) FROM PRODUCTQUANTMONITOR WHERE >>> FK1_SUPPLYID = NEWROW.FK1_SUPPLYID >>> AND TANKNUMBER = NEWROW.TANKNUMBER) >>> END >>> ; >>> >>> Hope this helps, >>> -Rick >>> >>> On 6/27/11 6:55 AM, IkeAbalogu wrote: Thanks.For your interest and help.Below is the entire schema -- -- Database creation Script -- -- Create a Database table to represent the "product" entity. CREATE TABLE product( nameVARCHAR(30) NOT NULL, description LONG VARCHAR NOT NULL, source LONG VARCHAR NOT NULL, -- Specify the PRIMARY KEY constraint for table "product". -- This indicates which attribute(s) uniquely identify each row of data. CONSTRAINT pk_product PRIMARY KEY (name) ); -- Create a Database table to represent the "productsupply" entity. CREATE TABLE productsupply( supplyidINTEGER NOT NULL, supply_date DATE NOT NULL, isfinished SMALLINT NOT NULL, finish_date DATE NOT NULL, quantityBIGINT NOT NULL, costprice DOUBLE PRECISION NOT NULL, saleprice DOUBLE PRECISION NOT NULL, fk1_nameVARCHAR(30) NOT NULL, -- Specify the PRIMARY KEY constraint for table "productsupply". -- This indicates which attribute(s) uniquely identify each row of data. CONSTRAINT pk_productsupply PRIMARY KEY (supplyid) ); -- Create a Database table to represent the "productquantmonitor" entity. CREATE TABLE productquantmonitor( checkdate DATE NOT NULL, tanknumber INTEGER NOT NULL, balance BIGINT NOT NULL, previous_balanceBIGINT NOT NULL, quantitysoldBIGINT NOT NULL, fk1_supplyidINTEGER NOT NULL ); -- Create a Database table to represent the "producttank" entity. CREATE TABLE producttank( tankid INTEGER NOT NULL, tankcapacityBIGINT NOT NULL, fk1_nameVARCHAR(30) NOT NULL, -- Specify the PRIMARY KEY constraint for table "producttank". -- This indicates which attribute(s) uniquely identify each row of data. CONSTRAINT pk_producttank PRIMARY KEY (tankid) ); -- -- Alter Tables to add fk constraints -- -- Now all the tables have been created the ALTER TABLE command is used to define some additional
Re: Error code 42X05. Table doesn't exist.Pls help with code.
IkeAbalogu writes: > Thanks.I see the error.I am updating newrow because it is the newly inserted > row that the trigger will update its previousbalance column.I will try to > come with a where statement to get to the newly inserted row.Your help will > still be appreciated. Perhaps you could add a primary key column to the PRODUCTQUANTMONITOR table and use that column in a WHERE clause in the UPDATE statement? The primary key could be automatically generated by Derby using an identity column: CREATE TABLE PRODUCTQUANTMONITOR ( ID INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, ...) -- Knut Anders
Re: Error code 42X05. Table doesn't exist.Pls help with code.
Instead of declaring a trigger which fires an update statement, you might want to consider declaring a trigger which fires a database procedure--the procedure would then update the row which you just inserted. The resulting code might be easier to reason about. Hope this helps, -Rick On 6/27/11 8:16 AM, IkeAbalogu wrote: Thanks.I see the error.I am updating newrow because it is the newly inserted row that the trigger will update its previousbalance column.I will try to come with a where statement to get to the newly inserted row.Your help will still be appreciated. Thanks again. Rick Hillegas-3 wrote: After running the database creation script, I see the reported 42X05 error when creating the problem trigger: CREATE TRIGGER NEWBALANCE AFTER INSERT ON APP.PRODUCTQUANTMONITOR REFERENCING NEW AS NEWROW FOR EACH ROW UPDATE NEWROW SET PREVIOUS_BALANCE = CASE WHEN (SELECT COUNT (NEWROW.FK1_SUPPLYID) FROM APP.PRODUCTQUANTMONITOR) = 1 THEN (SELECT QUANTITY FROM APP.PRODUCTSUPPLY WHERE SUPPLYID = NEWROW.FK1_SUPPLYID) ELSE (SELECT MIN(DISTINCT BALANCE) FROM PRODUCTQUANTMONITOR WHERE FK1_SUPPLYID = NEWROW.FK1_SUPPLYID AND TANKNUMBER = NEWROW.TANKNUMBER) END ; ERROR 42X05: Table/View 'NEWROW' does not exist. The error occurs because the triggered statement is trying to update the transient transition variable NEWROW rather than an actual table. The following rewritten trigger definition does compile and run cleanly. I don't know if this is what you are trying to achieve, though: CREATE TRIGGER NEWBALANCE AFTER INSERT ON APP.PRODUCTQUANTMONITOR REFERENCING NEW AS NEWROW FOR EACH ROW UPDATE APP.PRODUCTQUANTMONITOR SET PREVIOUS_BALANCE = CASE WHEN (SELECT COUNT (FK1_SUPPLYID) FROM APP.PRODUCTQUANTMONITOR) = 1 THEN (SELECT QUANTITY FROM APP.PRODUCTSUPPLY WHERE SUPPLYID = NEWROW.FK1_SUPPLYID) ELSE (SELECT MIN(DISTINCT BALANCE) FROM PRODUCTQUANTMONITOR WHERE FK1_SUPPLYID = NEWROW.FK1_SUPPLYID AND TANKNUMBER = NEWROW.TANKNUMBER) END ; Hope this helps, -Rick On 6/27/11 6:55 AM, IkeAbalogu wrote: Thanks.For your interest and help.Below is the entire schema -- -- Database creation Script -- -- Create a Database table to represent the "product" entity. CREATE TABLE product( nameVARCHAR(30) NOT NULL, description LONG VARCHAR NOT NULL, source LONG VARCHAR NOT NULL, -- Specify the PRIMARY KEY constraint for table "product". -- This indicates which attribute(s) uniquely identify each row of data. CONSTRAINT pk_product PRIMARY KEY (name) ); -- Create a Database table to represent the "productsupply" entity. CREATE TABLE productsupply( supplyidINTEGER NOT NULL, supply_date DATE NOT NULL, isfinished SMALLINT NOT NULL, finish_date DATE NOT NULL, quantityBIGINT NOT NULL, costprice DOUBLE PRECISION NOT NULL, saleprice DOUBLE PRECISION NOT NULL, fk1_nameVARCHAR(30) NOT NULL, -- Specify the PRIMARY KEY constraint for table "productsupply". -- This indicates which attribute(s) uniquely identify each row of data. CONSTRAINT pk_productsupply PRIMARY KEY (supplyid) ); -- Create a Database table to represent the "productquantmonitor" entity. CREATE TABLE productquantmonitor( checkdate DATE NOT NULL, tanknumber INTEGER NOT NULL, balance BIGINT NOT NULL, previous_balanceBIGINT NOT NULL, quantitysoldBIGINT NOT NULL, fk1_supplyidINTEGER NOT NULL ); -- Create a Database table to represent the "producttank" entity. CREATE TABLE producttank( tankid INTEGER NOT NULL, tankcapacityBIGINT NOT NULL, fk1_nameVARCHAR(30) NOT NULL, -- Specify the PRIMARY KEY constraint for table "producttank". -- This indicates which attribute(s) uniquely identify each row of data. CONSTRAINT pk_producttank PRIMARY KEY (tankid) ); -- -- Alter Tables to add fk constraints -- -- Now all the tables have been created the ALTER TABLE command is used to define some additional -- constraints. These typically constrain values of foreign keys to be associated in some way -- with the primary keys of related tables. Foreign key constraints can actually be specified -- when each table is created, but doing so can lead to dependency problems within the script -- i.e. tables may be referenced before they have been created. This method is therefore safer. -- Alter table to add new constraints required to implement the "producttank_product" relationship -- This constraint ensures that the foreign key of table "producttank" -- correctly references the primary key of table "product" ALTER TABLE producttank ADD CONSTRAINT fk1_producttank_to_product F
Re: Error code 42X05. Table doesn't exist.Pls help with code.
Thanks.I see the error.I am updating newrow because it is the newly inserted row that the trigger will update its previousbalance column.I will try to come with a where statement to get to the newly inserted row.Your help will still be appreciated. Thanks again. Rick Hillegas-3 wrote: > > After running the database creation script, I see the reported 42X05 > error when creating the problem trigger: > > CREATE TRIGGER NEWBALANCE > AFTER INSERT ON APP.PRODUCTQUANTMONITOR > REFERENCING NEW AS NEWROW > FOR EACH ROW > UPDATE NEWROW SET PREVIOUS_BALANCE = > CASE > WHEN (SELECT COUNT (NEWROW.FK1_SUPPLYID) FROM APP.PRODUCTQUANTMONITOR) = 1 > THEN (SELECT QUANTITY FROM APP.PRODUCTSUPPLY WHERE SUPPLYID = > NEWROW.FK1_SUPPLYID) > ELSE (SELECT MIN(DISTINCT BALANCE) FROM PRODUCTQUANTMONITOR WHERE > FK1_SUPPLYID = NEWROW.FK1_SUPPLYID > AND TANKNUMBER = NEWROW.TANKNUMBER) > END > ; > ERROR 42X05: Table/View 'NEWROW' does not exist. > > The error occurs because the triggered statement is trying to update the > transient transition variable NEWROW rather than an actual table. The > following rewritten trigger definition does compile and run cleanly. I > don't know if this is what you are trying to achieve, though: > > CREATE TRIGGER NEWBALANCE > AFTER INSERT ON APP.PRODUCTQUANTMONITOR > REFERENCING NEW AS NEWROW > FOR EACH ROW > UPDATE APP.PRODUCTQUANTMONITOR SET PREVIOUS_BALANCE = > CASE > WHEN (SELECT COUNT (FK1_SUPPLYID) FROM APP.PRODUCTQUANTMONITOR) = 1 > THEN (SELECT QUANTITY FROM APP.PRODUCTSUPPLY WHERE SUPPLYID = > NEWROW.FK1_SUPPLYID) > ELSE (SELECT MIN(DISTINCT BALANCE) FROM PRODUCTQUANTMONITOR WHERE > FK1_SUPPLYID = NEWROW.FK1_SUPPLYID > AND TANKNUMBER = NEWROW.TANKNUMBER) > END > ; > > Hope this helps, > -Rick > > On 6/27/11 6:55 AM, IkeAbalogu wrote: >> Thanks.For your interest and help.Below is the entire schema >> >> >> -- >> -- Database creation Script >> -- >> -- Create a Database table to represent the "product" entity. >> CREATE TABLE product( >> nameVARCHAR(30) NOT NULL, >> description LONG VARCHAR NOT NULL, >> source LONG VARCHAR NOT NULL, >> -- Specify the PRIMARY KEY constraint for table "product". >> -- This indicates which attribute(s) uniquely identify each row of data. >> CONSTRAINT pk_product PRIMARY KEY (name) >> ); >> >> -- Create a Database table to represent the "productsupply" entity. >> CREATE TABLE productsupply( >> supplyidINTEGER NOT NULL, >> supply_date DATE NOT NULL, >> isfinished SMALLINT NOT NULL, >> finish_date DATE NOT NULL, >> quantityBIGINT NOT NULL, >> costprice DOUBLE PRECISION NOT NULL, >> saleprice DOUBLE PRECISION NOT NULL, >> fk1_nameVARCHAR(30) NOT NULL, >> -- Specify the PRIMARY KEY constraint for table "productsupply". >> -- This indicates which attribute(s) uniquely identify each row of data. >> CONSTRAINT pk_productsupply PRIMARY KEY (supplyid) >> ); >> >> -- Create a Database table to represent the "productquantmonitor" entity. >> CREATE TABLE productquantmonitor( >> checkdate DATE NOT NULL, >> tanknumber INTEGER NOT NULL, >> balance BIGINT NOT NULL, >> previous_balanceBIGINT NOT NULL, >> quantitysoldBIGINT NOT NULL, >> fk1_supplyidINTEGER NOT NULL >> ); >> >> -- Create a Database table to represent the "producttank" entity. >> CREATE TABLE producttank( >> tankid INTEGER NOT NULL, >> tankcapacityBIGINT NOT NULL, >> fk1_nameVARCHAR(30) NOT NULL, >> -- Specify the PRIMARY KEY constraint for table "producttank". >> -- This indicates which attribute(s) uniquely identify each row of data. >> CONSTRAINT pk_producttank PRIMARY KEY (tankid) >> ); >> >> >> -- >> -- Alter Tables to add fk constraints -- >> >> -- Now all the tables have been created the ALTER TABLE command is used >> to >> define some additional >> -- constraints. These typically constrain values of foreign keys to be >> associated in some way >> -- with the primary keys of related tables. Foreign key constraints can >> actually be specified >> -- when each table is created, but doing so can lead to dependency >> problems >> within the script >> -- i.e. tables may be referenced before they have been created. This >> method >> is therefore safer. >> >> -- Alter table to add new constraints required to implement the >> "producttank_product" relationship >> >> -- This constraint ensures that the foreign key of table "producttank" >> -- correctly references the primary key of table "product" >> >> ALTER TABLE producttank ADD CONSTRAINT fk1_producttank_to_product FOREIGN >> KEY(fk1_name) REFERENCES product(name) ON DELETE RESTRICT ON UPDATE >> RESTRICT; >> >> -- Alter ta
Re: Error code 42X05. Table doesn't exist.Pls help with code.
After running the database creation script, I see the reported 42X05 error when creating the problem trigger: CREATE TRIGGER NEWBALANCE AFTER INSERT ON APP.PRODUCTQUANTMONITOR REFERENCING NEW AS NEWROW FOR EACH ROW UPDATE NEWROW SET PREVIOUS_BALANCE = CASE WHEN (SELECT COUNT (NEWROW.FK1_SUPPLYID) FROM APP.PRODUCTQUANTMONITOR) = 1 THEN (SELECT QUANTITY FROM APP.PRODUCTSUPPLY WHERE SUPPLYID = NEWROW.FK1_SUPPLYID) ELSE (SELECT MIN(DISTINCT BALANCE) FROM PRODUCTQUANTMONITOR WHERE FK1_SUPPLYID = NEWROW.FK1_SUPPLYID AND TANKNUMBER = NEWROW.TANKNUMBER) END ; ERROR 42X05: Table/View 'NEWROW' does not exist. The error occurs because the triggered statement is trying to update the transient transition variable NEWROW rather than an actual table. The following rewritten trigger definition does compile and run cleanly. I don't know if this is what you are trying to achieve, though: CREATE TRIGGER NEWBALANCE AFTER INSERT ON APP.PRODUCTQUANTMONITOR REFERENCING NEW AS NEWROW FOR EACH ROW UPDATE APP.PRODUCTQUANTMONITOR SET PREVIOUS_BALANCE = CASE WHEN (SELECT COUNT (FK1_SUPPLYID) FROM APP.PRODUCTQUANTMONITOR) = 1 THEN (SELECT QUANTITY FROM APP.PRODUCTSUPPLY WHERE SUPPLYID = NEWROW.FK1_SUPPLYID) ELSE (SELECT MIN(DISTINCT BALANCE) FROM PRODUCTQUANTMONITOR WHERE FK1_SUPPLYID = NEWROW.FK1_SUPPLYID AND TANKNUMBER = NEWROW.TANKNUMBER) END ; Hope this helps, -Rick On 6/27/11 6:55 AM, IkeAbalogu wrote: Thanks.For your interest and help.Below is the entire schema -- -- Database creation Script -- -- Create a Database table to represent the "product" entity. CREATE TABLE product( nameVARCHAR(30) NOT NULL, description LONG VARCHAR NOT NULL, source LONG VARCHAR NOT NULL, -- Specify the PRIMARY KEY constraint for table "product". -- This indicates which attribute(s) uniquely identify each row of data. CONSTRAINT pk_product PRIMARY KEY (name) ); -- Create a Database table to represent the "productsupply" entity. CREATE TABLE productsupply( supplyidINTEGER NOT NULL, supply_date DATE NOT NULL, isfinished SMALLINT NOT NULL, finish_date DATE NOT NULL, quantityBIGINT NOT NULL, costprice DOUBLE PRECISION NOT NULL, saleprice DOUBLE PRECISION NOT NULL, fk1_nameVARCHAR(30) NOT NULL, -- Specify the PRIMARY KEY constraint for table "productsupply". -- This indicates which attribute(s) uniquely identify each row of data. CONSTRAINT pk_productsupply PRIMARY KEY (supplyid) ); -- Create a Database table to represent the "productquantmonitor" entity. CREATE TABLE productquantmonitor( checkdate DATE NOT NULL, tanknumber INTEGER NOT NULL, balance BIGINT NOT NULL, previous_balanceBIGINT NOT NULL, quantitysoldBIGINT NOT NULL, fk1_supplyidINTEGER NOT NULL ); -- Create a Database table to represent the "producttank" entity. CREATE TABLE producttank( tankid INTEGER NOT NULL, tankcapacityBIGINT NOT NULL, fk1_nameVARCHAR(30) NOT NULL, -- Specify the PRIMARY KEY constraint for table "producttank". -- This indicates which attribute(s) uniquely identify each row of data. CONSTRAINT pk_producttank PRIMARY KEY (tankid) ); -- -- Alter Tables to add fk constraints -- -- Now all the tables have been created the ALTER TABLE command is used to define some additional -- constraints. These typically constrain values of foreign keys to be associated in some way -- with the primary keys of related tables. Foreign key constraints can actually be specified -- when each table is created, but doing so can lead to dependency problems within the script -- i.e. tables may be referenced before they have been created. This method is therefore safer. -- Alter table to add new constraints required to implement the "producttank_product" relationship -- This constraint ensures that the foreign key of table "producttank" -- correctly references the primary key of table "product" ALTER TABLE producttank ADD CONSTRAINT fk1_producttank_to_product FOREIGN KEY(fk1_name) REFERENCES product(name) ON DELETE RESTRICT ON UPDATE RESTRICT; -- Alter table to add new constraints required to implement the "productsupply_product" relationship -- This constraint ensures that the foreign key of table "productsupply" -- correctly references the primary key of table "product" ALTER TABLE productsupply ADD CONSTRAINT fk1_productsupply_to_product FOREIGN KEY(fk1_name) REFERENCES product(name) ON DELETE RESTRICT ON UPDATE RESTRICT; -- Alter table to add new constraints required to implement the "productquantmonitor_productsupply" relationship -- This constraint ens
Re: Error code 42X05. Table doesn't exist.Pls help with code.
Thanks.For your interest and help.Below is the entire schema -- -- Database creation Script -- -- Create a Database table to represent the "product" entity. CREATE TABLE product( nameVARCHAR(30) NOT NULL, description LONG VARCHAR NOT NULL, source LONG VARCHAR NOT NULL, -- Specify the PRIMARY KEY constraint for table "product". -- This indicates which attribute(s) uniquely identify each row of data. CONSTRAINT pk_product PRIMARY KEY (name) ); -- Create a Database table to represent the "productsupply" entity. CREATE TABLE productsupply( supplyidINTEGER NOT NULL, supply_date DATE NOT NULL, isfinished SMALLINT NOT NULL, finish_date DATE NOT NULL, quantityBIGINT NOT NULL, costprice DOUBLE PRECISION NOT NULL, saleprice DOUBLE PRECISION NOT NULL, fk1_nameVARCHAR(30) NOT NULL, -- Specify the PRIMARY KEY constraint for table "productsupply". -- This indicates which attribute(s) uniquely identify each row of data. CONSTRAINT pk_productsupply PRIMARY KEY (supplyid) ); -- Create a Database table to represent the "productquantmonitor" entity. CREATE TABLE productquantmonitor( checkdate DATE NOT NULL, tanknumber INTEGER NOT NULL, balance BIGINT NOT NULL, previous_balanceBIGINT NOT NULL, quantitysoldBIGINT NOT NULL, fk1_supplyidINTEGER NOT NULL ); -- Create a Database table to represent the "producttank" entity. CREATE TABLE producttank( tankid INTEGER NOT NULL, tankcapacityBIGINT NOT NULL, fk1_nameVARCHAR(30) NOT NULL, -- Specify the PRIMARY KEY constraint for table "producttank". -- This indicates which attribute(s) uniquely identify each row of data. CONSTRAINT pk_producttank PRIMARY KEY (tankid) ); -- -- Alter Tables to add fk constraints -- -- Now all the tables have been created the ALTER TABLE command is used to define some additional -- constraints. These typically constrain values of foreign keys to be associated in some way -- with the primary keys of related tables. Foreign key constraints can actually be specified -- when each table is created, but doing so can lead to dependency problems within the script -- i.e. tables may be referenced before they have been created. This method is therefore safer. -- Alter table to add new constraints required to implement the "producttank_product" relationship -- This constraint ensures that the foreign key of table "producttank" -- correctly references the primary key of table "product" ALTER TABLE producttank ADD CONSTRAINT fk1_producttank_to_product FOREIGN KEY(fk1_name) REFERENCES product(name) ON DELETE RESTRICT ON UPDATE RESTRICT; -- Alter table to add new constraints required to implement the "productsupply_product" relationship -- This constraint ensures that the foreign key of table "productsupply" -- correctly references the primary key of table "product" ALTER TABLE productsupply ADD CONSTRAINT fk1_productsupply_to_product FOREIGN KEY(fk1_name) REFERENCES product(name) ON DELETE RESTRICT ON UPDATE RESTRICT; -- Alter table to add new constraints required to implement the "productquantmonitor_productsupply" relationship -- This constraint ensures that the foreign key of table "productquantmonitor" -- correctly references the primary key of table "productsupply" ALTER TABLE productquantmonitor ADD CONSTRAINT fk1_productquantmonitor_to_productsupply FOREIGN KEY(fk1_supplyid) REFERENCES productsupply(supplyid) ON DELETE RESTRICT ON UPDATE RESTRICT; Thanks. Rick Hillegas-3 wrote: > > When I run these commands, I get an error on this one: > > ALTER TABLE productsupply ADD CONSTRAINT fk1_productsupply_to_product > FOREIGN KEY(fk1_name) REFERENCES product(name) ON DELETE RESTRICT ON > UPDATE > RESTRICT; > ERROR X0Y46: Constraint 'FK1_PRODUCTSUPPLY_TO_PRODUCT' is invalid: > referenced table PRODUCT does not exist. > > That seems reasonable because I don't see a table called PRODUCT in this > schema. Can you share the rest of your schema with us? > > Thanks, > -Rick > > On 6/27/11 6:22 AM, IkeAbalogu wrote: >> Thanks for your response.I get Error code 42X05. Table/view doesn't exist >> -- Create a Database table to represent the "productsupply" entity. >> CREATE TABLE productsupply( >> supplyidINTEGER NOT NULL, >> supply_date DATE NOT NULL, >> isfinished SMALLINT NOT NULL, >> finish_date DATE NOT NULL, >> quantityBIGINT NOT NULL, >> costprice DOUBLE PRECISION NOT NULL, >> saleprice DOUBLE PRECISION NOT NULL
Re: Error code 42X05. Table doesn't exist.Pls help with code.
When I run these commands, I get an error on this one: ALTER TABLE productsupply ADD CONSTRAINT fk1_productsupply_to_product FOREIGN KEY(fk1_name) REFERENCES product(name) ON DELETE RESTRICT ON UPDATE RESTRICT; ERROR X0Y46: Constraint 'FK1_PRODUCTSUPPLY_TO_PRODUCT' is invalid: referenced table PRODUCT does not exist. That seems reasonable because I don't see a table called PRODUCT in this schema. Can you share the rest of your schema with us? Thanks, -Rick On 6/27/11 6:22 AM, IkeAbalogu wrote: Thanks for your response.I get Error code 42X05. Table/view doesn't exist -- Create a Database table to represent the "productsupply" entity. CREATE TABLE productsupply( supplyidINTEGER NOT NULL, supply_date DATE NOT NULL, isfinished SMALLINT NOT NULL, finish_date DATE NOT NULL, quantityBIGINT NOT NULL, costprice DOUBLE PRECISION NOT NULL, saleprice DOUBLE PRECISION NOT NULL, fk1_nameVARCHAR(30) NOT NULL, -- Specify the PRIMARY KEY constraint for table "productsupply". -- This indicates which attribute(s) uniquely identify each row of data. CONSTRAINT pk_productsupply PRIMARY KEY (supplyid) ); -- Create a Database table to represent the "productquantmonitor" entity. CREATE TABLE productquantmonitor( checkdate DATE NOT NULL, tanknumber INTEGER NOT NULL, balance BIGINT NOT NULL, previous_balanceBIGINT NOT NULL, quantitysoldBIGINT NOT NULL, fk1_supplyidINTEGER NOT NULL ); -- Create a Database table to represent the "producttank" entity. CREATE TABLE producttank( tankid INTEGER NOT NULL, tankcapacityBIGINT NOT NULL, fk1_nameVARCHAR(30) NOT NULL, -- Specify the PRIMARY KEY constraint for table "producttank". -- This indicates which attribute(s) uniquely identify each row of data. CONSTRAINT pk_producttank PRIMARY KEY (tankid) ); -- This constraint ensures that the foreign key of table "productsupply" -- correctly references the primary key of table "product" ALTER TABLE productsupply ADD CONSTRAINT fk1_productsupply_to_product FOREIGN KEY(fk1_name) REFERENCES product(name) ON DELETE RESTRICT ON UPDATE RESTRICT; -- Alter table to add new constraints required to implement the "productquantmonitor_productsupply" relationship -- This constraint ensures that the foreign key of table "productquantmonitor" -- correctly references the primary key of table "productsupply" ALTER TABLE productquantmonitor ADD CONSTRAINT fk1_productquantmonitor_to_productsupply FOREIGN KEY(fk1_supplyid) REFERENCES productsupply(supplyid) ON DELETE RESTRICT ON UPDATE RESTRICT; Rick Hillegas-3 wrote: Can you share your schema and the error message you are seeing? That will help people advise you. Thanks, -Rick On 6/27/11 5:20 AM, IkeAbalogu wrote: CREATE TRIGGER NEWBALANCE AFTER INSERT ON APP.PRODUCTQUANTMONITOR REFERENCING NEW AS NEWROW FOR EACH ROW UPDATE NEWROW SET PREVIOUS_BALANCE = CASE WHEN (SELECT COUNT (NEWROW.FK1_SUPPLYID) FROM APP.PRODUCTQUANTMONITOR) = 1 THEN (SELECT QUANTITY FROM APP.PRODUCTSUPPLY WHERE SUPPLYID = NEWROW.FK1_SUPPLYID) ELSE (SELECT MIN(DISTINCT BALANCE) FROM PRODUCTQUANTMONITOR WHERE FK1_SUPPLYID = NEWROW.FK1_SUPPLYID AND TANKNUMBER = NEWROW.TANKNUMBER) END ;
Re: Error code 42X05. Table doesn't exist.Pls help with code.
Thanks for your response.I get Error code 42X05. Table/view doesn't exist -- Create a Database table to represent the "productsupply" entity. CREATE TABLE productsupply( supplyidINTEGER NOT NULL, supply_date DATE NOT NULL, isfinished SMALLINT NOT NULL, finish_date DATE NOT NULL, quantityBIGINT NOT NULL, costprice DOUBLE PRECISION NOT NULL, saleprice DOUBLE PRECISION NOT NULL, fk1_nameVARCHAR(30) NOT NULL, -- Specify the PRIMARY KEY constraint for table "productsupply". -- This indicates which attribute(s) uniquely identify each row of data. CONSTRAINT pk_productsupply PRIMARY KEY (supplyid) ); -- Create a Database table to represent the "productquantmonitor" entity. CREATE TABLE productquantmonitor( checkdate DATE NOT NULL, tanknumber INTEGER NOT NULL, balance BIGINT NOT NULL, previous_balanceBIGINT NOT NULL, quantitysoldBIGINT NOT NULL, fk1_supplyidINTEGER NOT NULL ); -- Create a Database table to represent the "producttank" entity. CREATE TABLE producttank( tankid INTEGER NOT NULL, tankcapacityBIGINT NOT NULL, fk1_nameVARCHAR(30) NOT NULL, -- Specify the PRIMARY KEY constraint for table "producttank". -- This indicates which attribute(s) uniquely identify each row of data. CONSTRAINT pk_producttank PRIMARY KEY (tankid) ); -- This constraint ensures that the foreign key of table "productsupply" -- correctly references the primary key of table "product" ALTER TABLE productsupply ADD CONSTRAINT fk1_productsupply_to_product FOREIGN KEY(fk1_name) REFERENCES product(name) ON DELETE RESTRICT ON UPDATE RESTRICT; -- Alter table to add new constraints required to implement the "productquantmonitor_productsupply" relationship -- This constraint ensures that the foreign key of table "productquantmonitor" -- correctly references the primary key of table "productsupply" ALTER TABLE productquantmonitor ADD CONSTRAINT fk1_productquantmonitor_to_productsupply FOREIGN KEY(fk1_supplyid) REFERENCES productsupply(supplyid) ON DELETE RESTRICT ON UPDATE RESTRICT; Rick Hillegas-3 wrote: > > Can you share your schema and the error message you are seeing? That > will help people advise you. > > Thanks, > -Rick > > On 6/27/11 5:20 AM, IkeAbalogu wrote: >> CREATE TRIGGER NEWBALANCE >> AFTER INSERT ON APP.PRODUCTQUANTMONITOR >> REFERENCING NEW AS NEWROW >> FOR EACH ROW >> UPDATE NEWROW SET PREVIOUS_BALANCE = >> CASE >> WHEN (SELECT COUNT (NEWROW.FK1_SUPPLYID) FROM APP.PRODUCTQUANTMONITOR) = >> 1 >> THEN (SELECT QUANTITY FROM APP.PRODUCTSUPPLY WHERE SUPPLYID = >> NEWROW.FK1_SUPPLYID) >> ELSE (SELECT MIN(DISTINCT BALANCE) FROM PRODUCTQUANTMONITOR WHERE >> FK1_SUPPLYID = NEWROW.FK1_SUPPLYID >> AND TANKNUMBER = NEWROW.TANKNUMBER) >> END >> ; > > > -- View this message in context: http://old.nabble.com/Error-code-42X05.-Table-doesn%27t-exist.Pls-help-with-code.-tp31936869p31937362.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Error code 42X05. Table doesn't exist.Pls help with code.
Can you share your schema and the error message you are seeing? That will help people advise you. Thanks, -Rick On 6/27/11 5:20 AM, IkeAbalogu wrote: CREATE TRIGGER NEWBALANCE AFTER INSERT ON APP.PRODUCTQUANTMONITOR REFERENCING NEW AS NEWROW FOR EACH ROW UPDATE NEWROW SET PREVIOUS_BALANCE = CASE WHEN (SELECT COUNT (NEWROW.FK1_SUPPLYID) FROM APP.PRODUCTQUANTMONITOR) = 1 THEN (SELECT QUANTITY FROM APP.PRODUCTSUPPLY WHERE SUPPLYID = NEWROW.FK1_SUPPLYID) ELSE (SELECT MIN(DISTINCT BALANCE) FROM PRODUCTQUANTMONITOR WHERE FK1_SUPPLYID = NEWROW.FK1_SUPPLYID AND TANKNUMBER = NEWROW.TANKNUMBER) END ;
Error code 42X05. Table doesn't exist.Pls help with code.
CREATE TRIGGER NEWBALANCE AFTER INSERT ON APP.PRODUCTQUANTMONITOR REFERENCING NEW AS NEWROW FOR EACH ROW UPDATE NEWROW SET PREVIOUS_BALANCE = CASE WHEN (SELECT COUNT (NEWROW.FK1_SUPPLYID) FROM APP.PRODUCTQUANTMONITOR) = 1 THEN (SELECT QUANTITY FROM APP.PRODUCTSUPPLY WHERE SUPPLYID = NEWROW.FK1_SUPPLYID) ELSE (SELECT MIN(DISTINCT BALANCE) FROM PRODUCTQUANTMONITOR WHERE FK1_SUPPLYID = NEWROW.FK1_SUPPLYID AND TANKNUMBER = NEWROW.TANKNUMBER) END ; -- View this message in context: http://old.nabble.com/Error-code-42X05.-Table-doesn%27t-exist.Pls-help-with-code.-tp31936869p31936869.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
