Executing commands with Association/Foreign Key in Stored Procedure

[Former Member]

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

[former_member185132 · Accepted solution]

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.

[Former Member · Accepted solution]

Hi Suhas,

"FATHER_ID.ASSETTYP_ID" is what I see in the Catalog but even after trying this, I get the same error.

I tried:

1.    select count(FATHER_ID.ID) into lv_rowcount

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

        where FATHER_ID.ASSETTYP_ID = :passettype_id;

2.    select count(FATHER_ID.ASSETTYP_ID) into lv_rowcount

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

        where FATHER_ID.ASSETTYP_ID = :passettype_id;

3.    select count(FATHER_ID) into lv_rowcount

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

        where FATHER_ID.ASSETTYP_ID = :passettype_id;

And all of these gave the same error.

Thanks!

[former_member185132 · Accepted solution]

Right-click on the table in the catalog, and click Generate -> Select statement. That gives you the exact field name and table name. From this you can copy the required fields, it's an easy way to avoid spelling mistakes.

[Former Member · Accepted solution]

Hi Suhas,

Apparently, I just needed to enclose them in double quotes, so it is working now.

Thanks anyway.