Skip to Content
author's profile photo Former Member
Former Member

Executing commands with Association/Foreign Key in Stored Procedure

Hi All,

I have created 2 tables using the *.hdbdd format below.

AssetType is the header table and Asset is the detail one.

Primary Key in AssetType table is FATHER_ID while ASSETTYP_ID is the foreign key in Asset table.

@Catalog.tableType : #COLUMN

Entity AssetType {

key ID: String (50);

key FATHER_ID: Association to Asset {ASSETTYP_ID};

ROLE_ID: String (50);

HPL_ORGANIZATION_UNIT_ID: String (50);

TEXT: String (200);

STRUCTURAL_ELEMENT: String (50);

FATHER_TEXT: String (50);

AVERAGE_AGE: LocalDate;

STDDEV_AVERAGE_AGE: String (50);

COMMENT: String (50);

};

@Catalog.tableType : #COLUMN

Entity Asset {

key ID: String (50);

FOREIGN_ID: String (50);

key ASSETTYP_ID: String (50);

STATUS_ID: String (50);

CITY_ID: String (50);

TEXT: String (200);

COMMENT: String (400);

CONSTRUCTION_YEAR: LocalDate;

TECHNICAL_DIRECTION: String (50);

SUBSTANCE: String (50);

NEXT_INSPECTION: LocalDate;

NEXT_MAINTENANCE: LocalDate;

};

And I have created a stored procedure that accepts some parameters, one of which is the passettype_id parameter which contains the value of a FATHER_ID. In the procedure, I would like to check if the value of the passettype_id parameter is already existing in the table before I do anything, thus:

PROCEDURE "AIP"."aip.procedures::AssetsInsertUpdate" (

IN passettype_id NVARCHAR(50),

...

BEGIN

declare lv_rowcount integer;

select count(FATHER_ID) into lv_rowcount ==> THIS IS LINE 33

from "AIP"."aip.data::Assets.AssetType" as AType

where AType.ASSETTYP_ID = :passettype_id;

...

END;

However, I get an error that says:

Error while activating /aip/procedures/AssetsInsertUpdate.hdbprocedure:

Could not create catalog object: invalid column name; FATHER_ID: line 33 col 18 (at pos 988)


What should the be the correct field name in my statements?

Similarly, I get the same error for my update statement that follows this select count command:

update "AIP"."aip.data::Assets.AssetType" as AssetTyp

set ID = :pheadassetid,

ROLE_ID = :prole_id,

...

COMMENT = :pheadcomment

where AssetTyp.ASSETTYP_ID = :passettype_id; ==> THIS IS LINE 52

Error:

Error while activating /aip/procedures/AssetsInsertUpdate.hdbprocedure:

Could not create catalog object: invalid column name; ASSETTYP.ASSETTYP_ID: line 52 col 24 (at pos 1796)

Please advise.

Thanks!

-Lyza

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Best Answer
    Posted on May 18, 2015 at 08:02 AM

    The field name should probably be:

    select count(FATHER_ID.ID) ...

    Also, the ASSETTYP.ASSETTYP_ID should be simply ASSETTYP.ID

    When in doubt, it's better to open the tables in the catalog and check the field names directly.


    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.