Click HERE to see how Saviynt Intelligence is transforming the industry. |
09/10/2024 11:20 PM
Hi ,
We have tried attached AccountImport XML as part of reconciliation for DB integration. The Query which we used in AccountImport XML is working fine in Data analyzer but this not working if we place in connector and running application import job. We can see below error throwing in failure job details.
"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1"
Can anyone suggest on this?
Solved! Go to Solution.
09/10/2024 11:32 PM
Can you make this one correct, I think you wanted it to be a.status
END AS astatus, (SELECT
09/11/2024 12:24 AM
Hi Amit,
This one alias name only and we used in correct way only. Please refer below screen shot.
09/11/2024 05:54 AM
use below and update security system name
<dataMapping>
<before-import></before-import>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="name" >
<![CDATA[ SELECT
u.username AS uname,
a.name AS acctName,
'Application' AS systemname,
'Application' AS endpointname,
CASE
WHEN a.status IN ('1' , 'Manually Provisioned') THEN '1'
WHEN a.status IN ('2' , 'Manually Suspended') THEN '2'
ELSE a.status
END AS astatus,
(SELECT
role_name
FROM
roles
WHERE
rolekey = result.role1) AS 'Role Name QAS',
(SELECT
customproperty6
FROM
roles
WHERE
rolekey = result.role1) AS 'cp6QAS',
(SELECT
customproperty7
FROM
roles
WHERE
rolekey = result.role1) AS 'cp7QAS',
(SELECT
customproperty8
FROM
roles
WHERE
rolekey = result.role1) AS 'cp8QAS',
(SELECT
customproperty9
FROM
roles
WHERE
rolekey = result.role1) AS 'cp9QAS',
(SELECT
customproperty10
FROM
roles
WHERE
rolekey = result.role1) AS 'cp10QAS',
(SELECT
customproperty11
FROM
roles
WHERE
rolekey = result.role1) AS 'cp11QAS',
(select case when customproperty13='Partner Role' then customproperty13 else a.customproperty6 end from roles where rolekey=result.role1) as 'partnerRoleQAS',
(SELECT
role_name
FROM
roles
WHERE
rolekey = result.role2) AS 'Role Name STG',
(SELECT
customproperty6
FROM
roles
WHERE
rolekey = result.role2) AS 'cp6STG',
(SELECT
customproperty7
FROM
roles
WHERE
rolekey = result.role2) AS 'cp7STG',
(SELECT
customproperty8
FROM
roles
WHERE
rolekey = result.role2) AS 'cp8STG',
(SELECT
customproperty9
FROM
roles
WHERE
rolekey = result.role2) AS 'cp9STG',
(SELECT
customproperty10
FROM
roles
WHERE
rolekey = result.role2) AS 'cp10STG',
(SELECT
customproperty11
FROM
roles
WHERE
rolekey = result.role2) AS 'cp11STG',
(select case when customproperty13='Partner Role' then customproperty13 else a.customproperty6 end from roles where rolekey=result.role2) as 'partnerRoleSTG'
FROM
users u,
user_accounts ua,
accounts a,
(SELECT
finalResult.aKey AS acctKey,
MAX(CASE
WHEN finalResult.rName1 LIKE '%QAS%' THEN finalResult.rKey
END) AS role1,
MAX(CASE
WHEN finalResult.rName1 LIKE '%STG%' THEN finalResult.rKey
END) AS role2
FROM
(SELECT
a.name AS acctName,
rua.accountkey AS aKey,
rua.rolekey AS rKey,
r.role_name AS rName1
FROM
accounts a
LEFT JOIN role_user_account rua ON rua.accountkey = a.accountkey
INNER JOIN roles r ON r.rolekey = rua.rolekey
WHERE
r.role_name LIKE '%QAS%'
AND r.endpointkey = 175
AND a.endpointkey = 175
AND a.status NOT IN ('SUSPENDED FROM IMPORT SERVICE') UNION SELECT
a.name AS acctName,
rua.accountkey,
rua.rolekey AS rKey,
r.role_name AS rName1
FROM
accounts a
LEFT JOIN role_user_account rua ON rua.accountkey = a.accountkey
INNER JOIN roles r ON r.rolekey = rua.rolekey
WHERE
r.role_name LIKE '%STG%'
AND r.endpointkey = 175
AND a.endpointkey = 175
AND a.status NOT IN ('SUSPENDED FROM IMPORT SERVICE')) finalResult
GROUP BY finalResult.acctName) result
WHERE
u.userkey = ua.userkey
AND ua.accountkey = a.accountkey
AND a.accountkey = result.acctKey
union
(
SELECT
u.username AS uname,
a.name AS acctName,
'Application' AS systemname,
'Application' AS endpointname,
CASE WHEN a.status IN ('1', 'Manually Provisioned') THEN '1' WHEN a.status IN ('2', 'Manually Suspended') THEN '2' ELSE a.status END AS astatus ,
'' AS 'Role Name QAS',
'' AS 'cp6QAS',
'' AS 'cp7QAS',
'' AS 'cp8QAS',
'' AS 'cp9QAS',
'' AS 'cp10QAS',
'' AS 'cp11QAS',
'' AS 'partnerRoleQAS',
'' AS 'Role Name STG',
'' AS 'cp6STG',
'' AS 'cp7STG',
'' AS 'cp8STG',
'' AS 'cp9STG',
'' AS 'cp10STG',
'' AS 'cp11STG',
'' AS 'partnerRoleSTG'
FROM
users u,
user_accounts ua,
accounts a
where
u.userkey = ua.userkey
AND ua.accountkey = a.accountkey
and a.endpointkey = 175
AND a.status NOT IN ('SUSPENDED FROM IMPORT SERVICE')
AND u.userkey not in (Select userkey from role_user_account,roles where role_user_account.rolekey=roles.rolekey and roles.endpointkey=175)
) ]]>
</sql-query>
<mapper description="Database Accounts and Account to Entitlement Import" accountnotinfileaction="suspend" deleteaccountentitlement="true" dateformat="date" systems="Rushi_DB'">
<mapfield saviyntproperty="accounts.name" sourceproperty="acctName" type="character"></mapfield>
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="systemname" type="character"></mapfield>
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpointname" type="character"></mapfield>
<mapfield saviyntproperty="users.username" sourceproperty="uname" type="character"></mapfield>
<mapfield saviyntproperty="accounts.customproperty5" sourceproperty="cp7QAS" type="character"></mapfield>
<mapfield saviyntproperty="accounts.customproperty4" sourceproperty="cp11QAS" type="character"></mapfield>
<mapfield saviyntproperty="accounts.customproperty6" sourceproperty="partnerRoleQAS" type="character"></mapfield>
<mapfield saviyntproperty="accounts.customproperty1" sourceproperty="cp6QAS" type="character"></mapfield>
<mapfield saviyntproperty="accounts.customproperty1" sourceproperty="cp6STG" type="character"></mapfield>
<mapfield saviyntproperty="accounts.customproperty10" sourceproperty="cp8QAS" type="character"></mapfield>
<mapfield saviyntproperty="accounts.customproperty11" sourceproperty="cp9QAS" type="character"></mapfield>
<mapfield saviyntproperty="accounts.customproperty12" sourceproperty="cp10QAS" type="character"></mapfield>
<mapfield saviyntproperty="accounts.customproperty13" sourceproperty="astatus" type="character"></mapfield>
<mapfield saviyntproperty="accounts.customproperty14" sourceproperty="Role Name QAS" type="character"></mapfield>
<mapfield saviyntproperty="accounts.customproperty15" sourceproperty="cp7STG" type="character"></mapfield>
<mapfield saviyntproperty="accounts.customproperty16" sourceproperty="cp11STG" type="character"></mapfield>
<mapfield saviyntproperty="accounts.customproperty17" sourceproperty="partnerRoleSTG" type="character"></mapfield>
<mapfield saviyntproperty="accounts.customproperty18" sourceproperty="cp8STG" type="character"></mapfield>
<mapfield saviyntproperty="accounts.customproperty19" sourceproperty="cp9STG" type="character"></mapfield>
<mapfield saviyntproperty="accounts.customproperty20" sourceproperty="cp10STG" type="character"></mapfield>
<mapfield saviyntproperty="accounts.customproperty21" sourceproperty="Role Name STG" type="character"></mapfield>
</mapper>
<after-import description="EMAIL,BATCH,SQL"></after-import>
</dataMapping>
09/11/2024 06:11 AM
Thank you for the solution. Hardcoded securitysystem name added in alias wrong in the file hence it was failing while mapping. Now we concluded that securitysystem mapping issue.