AccountImport xml is not working for DB connector where as Query is working in Data analyzer

[Himachal]

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?

[Amit_Malik]

Can you make this one correct, I think you wanted it to be a.status

END AS astatus,
    (SELECT

[Himachal]

Hi Amit,

This one alias name only and we used in correct way only. Please refer below screen shot.

[rushikeshvartak · Accepted solution]

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>

[Himachal]

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.