You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by IkeAbalogu <ik...@hotmail.com> on 2011/06/27 14:20:23 UTC
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.
Re: Error code 42X05. Table doesn't exist.Pls help with code.
Posted by Rick Hillegas <ri...@oracle.com>.
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(
>>>>> name VARCHAR(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(
>>>>> supplyid INTEGER NOT NULL,
>>>>> supply_date DATE NOT NULL,
>>>>> isfinished SMALLINT NOT NULL,
>>>>> finish_date DATE NOT NULL,
>>>>> quantity BIGINT NOT NULL,
>>>>> costprice DOUBLE PRECISION NOT NULL,
>>>>> saleprice DOUBLE PRECISION NOT NULL,
>>>>> fk1_name VARCHAR(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_balance BIGINT NOT NULL,
>>>>> quantitysold BIGINT NOT NULL,
>>>>> fk1_supplyid INTEGER NOT NULL
>>>>> );
>>>>>
>>>>> -- Create a Database table to represent the "producttank" entity.
>>>>> CREATE TABLE producttank(
>>>>> tankid INTEGER NOT NULL,
>>>>> tankcapacity BIGINT NOT NULL,
>>>>> fk1_name VARCHAR(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(
>>>>>>> supplyid INTEGER NOT NULL,
>>>>>>> supply_date DATE NOT NULL,
>>>>>>> isfinished SMALLINT NOT NULL,
>>>>>>> finish_date DATE NOT NULL,
>>>>>>> quantity BIGINT NOT NULL,
>>>>>>> costprice DOUBLE PRECISION NOT NULL,
>>>>>>> saleprice DOUBLE PRECISION NOT NULL,
>>>>>>> fk1_name VARCHAR(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_balance BIGINT NOT NULL,
>>>>>>> quantitysold BIGINT NOT NULL,
>>>>>>> fk1_supplyid INTEGER NOT NULL
>>>>>>> );
>>>>>>>
>>>>>>> -- Create a Database table to represent the "producttank" entity.
>>>>>>> CREATE TABLE producttank(
>>>>>>> tankid INTEGER NOT NULL,
>>>>>>> tankcapacity BIGINT NOT NULL,
>>>>>>> fk1_name VARCHAR(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.
Posted by IkeAbalogu <ik...@hotmail.com>.
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(
>>>> name VARCHAR(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(
>>>> supplyid INTEGER NOT NULL,
>>>> supply_date DATE NOT NULL,
>>>> isfinished SMALLINT NOT NULL,
>>>> finish_date DATE NOT NULL,
>>>> quantity BIGINT NOT NULL,
>>>> costprice DOUBLE PRECISION NOT NULL,
>>>> saleprice DOUBLE PRECISION NOT NULL,
>>>> fk1_name VARCHAR(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_balance BIGINT NOT NULL,
>>>> quantitysold BIGINT NOT NULL,
>>>> fk1_supplyid INTEGER NOT NULL
>>>> );
>>>>
>>>> -- Create a Database table to represent the "producttank" entity.
>>>> CREATE TABLE producttank(
>>>> tankid INTEGER NOT NULL,
>>>> tankcapacity BIGINT NOT NULL,
>>>> fk1_name VARCHAR(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(
>>>>>> supplyid INTEGER NOT NULL,
>>>>>> supply_date DATE NOT NULL,
>>>>>> isfinished SMALLINT NOT NULL,
>>>>>> finish_date DATE NOT NULL,
>>>>>> quantity BIGINT NOT NULL,
>>>>>> costprice DOUBLE PRECISION NOT NULL,
>>>>>> saleprice DOUBLE PRECISION NOT NULL,
>>>>>> fk1_name VARCHAR(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_balance BIGINT NOT NULL,
>>>>>> quantitysold BIGINT NOT NULL,
>>>>>> fk1_supplyid INTEGER NOT NULL
>>>>>> );
>>>>>>
>>>>>> -- Create a Database table to represent the "producttank" entity.
>>>>>> CREATE TABLE producttank(
>>>>>> tankid INTEGER NOT NULL,
>>>>>> tankcapacity BIGINT NOT NULL,
>>>>>> fk1_name VARCHAR(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.-tp31936869p31945490.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.
Posted by Rick Hillegas <ri...@oracle.com>.
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(
>>> name VARCHAR(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(
>>> supplyid INTEGER NOT NULL,
>>> supply_date DATE NOT NULL,
>>> isfinished SMALLINT NOT NULL,
>>> finish_date DATE NOT NULL,
>>> quantity BIGINT NOT NULL,
>>> costprice DOUBLE PRECISION NOT NULL,
>>> saleprice DOUBLE PRECISION NOT NULL,
>>> fk1_name VARCHAR(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_balance BIGINT NOT NULL,
>>> quantitysold BIGINT NOT NULL,
>>> fk1_supplyid INTEGER NOT NULL
>>> );
>>>
>>> -- Create a Database table to represent the "producttank" entity.
>>> CREATE TABLE producttank(
>>> tankid INTEGER NOT NULL,
>>> tankcapacity BIGINT NOT NULL,
>>> fk1_name VARCHAR(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(
>>>>> supplyid INTEGER NOT NULL,
>>>>> supply_date DATE NOT NULL,
>>>>> isfinished SMALLINT NOT NULL,
>>>>> finish_date DATE NOT NULL,
>>>>> quantity BIGINT NOT NULL,
>>>>> costprice DOUBLE PRECISION NOT NULL,
>>>>> saleprice DOUBLE PRECISION NOT NULL,
>>>>> fk1_name VARCHAR(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_balance BIGINT NOT NULL,
>>>>> quantitysold BIGINT NOT NULL,
>>>>> fk1_supplyid INTEGER NOT NULL
>>>>> );
>>>>>
>>>>> -- Create a Database table to represent the "producttank" entity.
>>>>> CREATE TABLE producttank(
>>>>> tankid INTEGER NOT NULL,
>>>>> tankcapacity BIGINT NOT NULL,
>>>>> fk1_name VARCHAR(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.
Posted by Knut Anders Hatlen <kn...@oracle.com>.
IkeAbalogu <ik...@hotmail.com> 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.
Posted by IkeAbalogu <ik...@hotmail.com>.
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(
>> name VARCHAR(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(
>> supplyid INTEGER NOT NULL,
>> supply_date DATE NOT NULL,
>> isfinished SMALLINT NOT NULL,
>> finish_date DATE NOT NULL,
>> quantity BIGINT NOT NULL,
>> costprice DOUBLE PRECISION NOT NULL,
>> saleprice DOUBLE PRECISION NOT NULL,
>> fk1_name VARCHAR(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_balance BIGINT NOT NULL,
>> quantitysold BIGINT NOT NULL,
>> fk1_supplyid INTEGER NOT NULL
>> );
>>
>> -- Create a Database table to represent the "producttank" entity.
>> CREATE TABLE producttank(
>> tankid INTEGER NOT NULL,
>> tankcapacity BIGINT NOT NULL,
>> fk1_name VARCHAR(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(
>>>> supplyid INTEGER NOT NULL,
>>>> supply_date DATE NOT NULL,
>>>> isfinished SMALLINT NOT NULL,
>>>> finish_date DATE NOT NULL,
>>>> quantity BIGINT NOT NULL,
>>>> costprice DOUBLE PRECISION NOT NULL,
>>>> saleprice DOUBLE PRECISION NOT NULL,
>>>> fk1_name VARCHAR(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_balance BIGINT NOT NULL,
>>>> quantitysold BIGINT NOT NULL,
>>>> fk1_supplyid INTEGER NOT NULL
>>>> );
>>>>
>>>> -- Create a Database table to represent the "producttank" entity.
>>>> CREATE TABLE producttank(
>>>> tankid INTEGER NOT NULL,
>>>> tankcapacity BIGINT NOT NULL,
>>>> fk1_name VARCHAR(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.-tp31936869p31938244.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.
Posted by Rick Hillegas <ri...@oracle.com>.
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(
> name VARCHAR(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(
> supplyid INTEGER NOT NULL,
> supply_date DATE NOT NULL,
> isfinished SMALLINT NOT NULL,
> finish_date DATE NOT NULL,
> quantity BIGINT NOT NULL,
> costprice DOUBLE PRECISION NOT NULL,
> saleprice DOUBLE PRECISION NOT NULL,
> fk1_name VARCHAR(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_balance BIGINT NOT NULL,
> quantitysold BIGINT NOT NULL,
> fk1_supplyid INTEGER NOT NULL
> );
>
> -- Create a Database table to represent the "producttank" entity.
> CREATE TABLE producttank(
> tankid INTEGER NOT NULL,
> tankcapacity BIGINT NOT NULL,
> fk1_name VARCHAR(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(
>>> supplyid INTEGER NOT NULL,
>>> supply_date DATE NOT NULL,
>>> isfinished SMALLINT NOT NULL,
>>> finish_date DATE NOT NULL,
>>> quantity BIGINT NOT NULL,
>>> costprice DOUBLE PRECISION NOT NULL,
>>> saleprice DOUBLE PRECISION NOT NULL,
>>> fk1_name VARCHAR(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_balance BIGINT NOT NULL,
>>> quantitysold BIGINT NOT NULL,
>>> fk1_supplyid INTEGER NOT NULL
>>> );
>>>
>>> -- Create a Database table to represent the "producttank" entity.
>>> CREATE TABLE producttank(
>>> tankid INTEGER NOT NULL,
>>> tankcapacity BIGINT NOT NULL,
>>> fk1_name VARCHAR(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.
Posted by IkeAbalogu <ik...@hotmail.com>.
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(
name VARCHAR(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(
supplyid INTEGER NOT NULL,
supply_date DATE NOT NULL,
isfinished SMALLINT NOT NULL,
finish_date DATE NOT NULL,
quantity BIGINT NOT NULL,
costprice DOUBLE PRECISION NOT NULL,
saleprice DOUBLE PRECISION NOT NULL,
fk1_name VARCHAR(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_balance BIGINT NOT NULL,
quantitysold BIGINT NOT NULL,
fk1_supplyid INTEGER NOT NULL
);
-- Create a Database table to represent the "producttank" entity.
CREATE TABLE producttank(
tankid INTEGER NOT NULL,
tankcapacity BIGINT NOT NULL,
fk1_name VARCHAR(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(
>> supplyid INTEGER NOT NULL,
>> supply_date DATE NOT NULL,
>> isfinished SMALLINT NOT NULL,
>> finish_date DATE NOT NULL,
>> quantity BIGINT NOT NULL,
>> costprice DOUBLE PRECISION NOT NULL,
>> saleprice DOUBLE PRECISION NOT NULL,
>> fk1_name VARCHAR(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_balance BIGINT NOT NULL,
>> quantitysold BIGINT NOT NULL,
>> fk1_supplyid INTEGER NOT NULL
>> );
>>
>> -- Create a Database table to represent the "producttank" entity.
>> CREATE TABLE producttank(
>> tankid INTEGER NOT NULL,
>> tankcapacity BIGINT NOT NULL,
>> fk1_name VARCHAR(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.-tp31936869p31937684.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.
Posted by Rick Hillegas <ri...@oracle.com>.
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(
> supplyid INTEGER NOT NULL,
> supply_date DATE NOT NULL,
> isfinished SMALLINT NOT NULL,
> finish_date DATE NOT NULL,
> quantity BIGINT NOT NULL,
> costprice DOUBLE PRECISION NOT NULL,
> saleprice DOUBLE PRECISION NOT NULL,
> fk1_name VARCHAR(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_balance BIGINT NOT NULL,
> quantitysold BIGINT NOT NULL,
> fk1_supplyid INTEGER NOT NULL
> );
>
> -- Create a Database table to represent the "producttank" entity.
> CREATE TABLE producttank(
> tankid INTEGER NOT NULL,
> tankcapacity BIGINT NOT NULL,
> fk1_name VARCHAR(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.
Posted by IkeAbalogu <ik...@hotmail.com>.
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(
supplyid INTEGER NOT NULL,
supply_date DATE NOT NULL,
isfinished SMALLINT NOT NULL,
finish_date DATE NOT NULL,
quantity BIGINT NOT NULL,
costprice DOUBLE PRECISION NOT NULL,
saleprice DOUBLE PRECISION NOT NULL,
fk1_name VARCHAR(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_balance BIGINT NOT NULL,
quantitysold BIGINT NOT NULL,
fk1_supplyid INTEGER NOT NULL
);
-- Create a Database table to represent the "producttank" entity.
CREATE TABLE producttank(
tankid INTEGER NOT NULL,
tankcapacity BIGINT NOT NULL,
fk1_name VARCHAR(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.
Posted by Rick Hillegas <ri...@oracle.com>.
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
> ;