Click HERE to see how Saviynt Intelligence is transforming the industry. |
07/30/2024 10:44 PM
Hi,
We have a requirement where we need to update accounts table with the referenced_accountname and referenced_accountkey based on the details from user and accounts table.
Now I have a select statement with gives referenced_accountname and referenced_accountkey:
select u.username, u.customproperty18 as Referenced_Accountname, a1.accountkey as Referenced_Accountkey, a.name, a.endpointkey, a.accountkey, e.endpointname from accounts a, users u, user_accounts ua, endpoints e, accounts a1 where ua.userkey = u.userkey and ua.accountkey = a.accountkey and a.endpointkey = e.endpointkey and a1.endpointkey = a.endpointkey and u.customproperty18=a1.name and u.customproperty18 is not null and e.endpointname like 'SAP%'
Below is the screenshot:
The basic enhanced_query to update referenced_accountname and referenced_accountkey is:
select 'fl-Sales' as accounts__REFERENCED_ACCOUNTNAME, '5403' as accounts__REFERENCED_ACCOUNTKEY, accountkey as accounts__PRIMARYKEY from accounts where accountkey = '5850'
Please confirm how the above select query can be used in the enhancedquery?
Solved! Go to Solution.
07/30/2024 10:56 PM
Yes you can use enhanced query
07/30/2024 11:01 PM
The query I gave is a working query, I wanted to merge both the select statement and the enhanced query.
Below is working for me:
select t.Referenced_Accountname as accounts__REFERENCED_ACCOUNTNAME, t.Referenced_Accountkey as accounts__REFERENCED_ACCOUNTKEY, accounts.accountkey as accounts__PRIMARYKEY from accounts, (select u.username, u.customproperty18 as Referenced_Accountname, a1.accountkey as Referenced_Accountkey, a.name, a.endpointkey, a.accountkey, e.endpointname from accounts a, users u, user_accounts ua, endpoints e, accounts a1 where ua.userkey = u.userkey and ua.accountkey = a.accountkey and a.endpointkey = e.endpointkey and a1.endpointkey = a.endpointkey and u.customproperty18=a1.name and u.customproperty18 is not null and e.endpointname like 'SAP%') t where accounts.accountkey = t.accountkey
07/30/2024 11:12 PM
SELECT
'fl-Sales' AS accounts__REFERENCED_ACCOUNTNAME,
'5403' AS accounts__REFERENCED_ACCOUNTKEY,
accountkey AS accounts__PRIMARYKEY
FROM
accounts
WHERE
accountkey = '5850'
UNION
SELECT
t.Referenced_Accountname AS accounts__REFERENCED_ACCOUNTNAME,
t.Referenced_Accountkey AS accounts__REFERENCED_ACCOUNTKEY,
accounts.accountkey AS accounts__PRIMARYKEY
FROM
accounts,
(
SELECT
u.username,
u.customproperty18 AS Referenced_Accountname,
a1.accountkey AS Referenced_Accountkey,
a.name,
a.endpointkey,
a.accountkey,
e.endpointname
FROM
accounts a
INNER JOIN user_accounts ua ON ua.accountkey = a.accountkey
INNER JOIN users u ON ua.userkey = u.userkey
INNER JOIN endpoints e ON a.endpointkey = e.endpointkey
INNER JOIN accounts a1 ON a1.endpointkey = a.endpointkey
WHERE
u.customproperty18 = a1.name
AND u.customproperty18 IS NOT NULL
AND e.endpointname LIKE 'SAP%'
) t
WHERE
accounts.accountkey = t.accountkey