How to associate a column of table from one hdi container to foreign hdi container

[former_member597564]

How to associate a column of table from one hdi container to another column of different hdi container using hdbcds

[thomas_jung · Accepted solution]

You will need to create a synonym to the foreign container and the use the USING command to import the object in the hdbcds. There is an example here using a foreign schema object, the basic approach is the same for foreign container object as well.

https://github.com/SAP/hana-xsa-opensap-hana7/blob/master/db/src/data/SFlight/FLIGHT.hdbcds

[former_member597564 · Accepted solution]

Hi Thomas,

I have created synonym and used using command but still getting error

Cannot find artifact "adt_toolshed.adt_db.synonyms::SRC_SYSTEM" [1310042]

key "ASSOC_SRC_SYSTEM" : association to SRC_SYSTEM { SRC_SYSTEM_ID } not null;

Regards,

Nagendra

[thomas_jung]

So the view on the imported object works fine? If so do the association like the sample I provided above with the mixin syntax. You can’t just do the direct association like you would with a local entity.

[thomas_jung]

SRC1 is the key in the "Test" entity that you are joining to the ID column of the TESTVIEW.

[former_member597564]

Thanks Thomas for your support

[thomas_jung]

I don't have you exact tables/views, but recreated this scenario with a synonym pointing to a table in a foreign container. I imported that synonym into my hdbcds, added a view on it and then built the association from a local entity to this view. It builds and deploys fine. I didn't even have to resort to the mixin. I was able to use an unmanaged association directly.

using "UserData.User" as USER;


context UserEx {
    entity "test" {
        "SRC1"   : Integer;
        "SUB_TP" : String(32);
        "AJ_1"   : String(256);
        "ASSOC"  : association[0..*] to TESTVIEW on SRC1 = ASSOC.ID;
    }


    view TESTVIEW as
        select from USER
        {
            "UserId"     as ID,
            "FirstName",
            "LastName"
        };
};

If you want to see the example within context of the whole project (although its a large project): https://github.com/SAP/hana-xsa-opensap-hana7/blob/next_prep/db/src/data/User/UserEx.hdbcds

[former_member597564]

Hi Thomas,

I need to understand the on condition which you have written.

on SRC1 = ASSOC.ID;

Here I want to know why you have chosen SRC1 here?

Does it mean that

it will fetch the values of Target entity

based on values of source entity?

Regards,

Nagendra

[thomas_jung]

Not really enough information here to comment. Does your synonym work if you preview the data in the database explorer? Can you just create a view on this synonym in HDBCDS? What does your source code even look like?

[former_member597564]

Hi Thomas ,

I can view the data in db explorer.

After creating view in hdbcds.

Then how to associate with column of view?

Regards

Nagendra

[former_member597564]

Hi Thomas ,

I have created view on synonym and i have build it successfully.Please assist further

my source code looks like this

i need to create association from 'assoc' to filed of view

namespace a.db.tables;

using "a.db.synonyms::SRC_STM" as SRCSTM;

context srcstm

{

entity "test"

{

"SRC1" : String(32) default 'NULL';

"SUB_TP" : String(32);

"AJ_1" : String(256);

"assoc" : association[0..1] to .....................

}

technical configuration

{ column store; };

/*@@layout{"layoutInfo":{"x":58,"y":-98.5}}*/

view View_1 as select from SRC_STM { SRC_STM_ID }

};

}