Custom join query to Enhanced Query format

[CR]

Hi Team,

Could you help on below Query design for EH

Custom Query:

update
entitlement_owners
inner join entitlement_values on entitlement_owners.ENTITLEMENT_VALUEKEY = entitlement_values.ENTITLEMENT_VALUEKEY
and entitlement_values.ENTITLEMENTTYPEKEY = 241
inner join accounts on entitlement_values.customproperty7 = accounts.ACCOUNTID
and accounts.endpointkey = 84
inner join user_accounts on accounts.accountkey = user_accounts.accountkey
inner join users on user_accounts.userkey = users.userkey
set
entitlement_owners.USERKEY = users.USERKEY;

Update
roles as r
INNER JOIN entitlement_values as e ON e.entitlement_value = r.role_name
INNER JOIN accounts as a ON e.customproperty7 = a.ACCOUNTID
and a.endpointkey = 84
set
r.CUSTOMPROPERTY4 = a.name;

Insert into
entitlement_owners (userkey, entitlement_valuekey, Rank)
select
1,
entitlement_valuekey,
2
from
entitlement_values
where
entitlement_valuekey not in (
select
distinct ENTITLEMENT_VALUEKEY
from
entitlement_owners
)
and CUSTOMPROPERTY10 like 'SaviyntManaged'
and CUSTOMPROPERTY7 is not null;

Team,

First query we using "entitlement_owners" table but there was primary key's two available , can you suggest how we can go? EH CQ design ?

Table : entitlement_owners , primary keys 1.USERKEY , 2.ENTITLEMENT_VALUEKEY

Thanks,

Raghu

[This message has been edited by moderator to merge reply comment]

[sudeshjaiswal]

Hello @CR,

Please use this SAMPLE, to form the above query,
We use the combination of table and Column along with "__" (underscore). We also tried to find the primary key of that table to along with that coloumn. This is the sample i create for the ars_requests table.

select current_timestamp() as request_access__STARTDATE ,REQUEST_ACCESSKEY as request_access__primaryKey from request_access where REQUEST_ACCESSKEY in (select ars_requests.REQUESTKEY from ars_requests where JBPMPROCESSINSTANCEID like '%2980382%')

  Thanks

[CR]

Hi @sudeshjaiswal ,

Thanks resp.

Actually if you look first query  entitlement_owners table we have 2 primary keys , not able form query , as EH it should allow one primary key at the time of design.

can you confirm how can we use two primary keys in one table?

Thanks,

Raghu

[rushikeshvartak]

You have differentiate with tablename_Primarykey

[CR]

Yes, we are using table with primary key only, one table with 1 primary key it work but  have 2 primary keys one table , this scenario how can achieve EQ query form? this our requirement

if can you check provided table you will find schema

Table : entitlement_owners , primary keys 1.USERKEY , 2.ENTITLEMENT_VALUEKEY

[rushikeshvartak]

Currently, the existing feature does not possess the ability to update tables featuring composite primary keys. The following tables cannot undergo updates due to this inherent constraint:

• https://docs.saviyntcloud.com/bundle/EIC-Admin-v23x/page/Content/Chapter10-Job-Control-Panel/Job-Cat...

[CR]

Thanks @rushikeshvartak .

 Any alternative approach for updating those query's or not? could you confirm please?

[rushikeshvartak · Accepted solution]

No alternative raise enhancement ticket