Multiple EntitlementTypes Import for Database connector

[gaurav_wagh]

Hi Team,

We have two types of entitlement "Company_code" and "Roles". Both from different tables and column as well.

Below is the query :

Company Code Import - select company_code AS ROLENAME,'Company_Code' as entitlementtype,'XYZ'
as securitysystem,'XYZ' as
endpoint,1 as status from company_codes

Roles Import - select role_type AS ROLENAME,'Roles' as entitlementtype, 'XYZ' as securitysystem,'XYZ' as endpoint,1 as status , id as entID from role_types

Now the role_types is character and company code is int. If I run both queries using UNION its giving me below issue. 

Error : 

ERROR: UNION types character varying and integer cannot be matched Position: 455

ENTITLEMENTVALUEIMPORT : 

<dataMapping>
<sql-query description="This is the Source Database Query">
<![CDATA[
select company_code AS ROLENAME,'Company_Code' as entitlementtype,'XYZ' as securitysystem,'XYZ' as endpoint,1 as status , company_code as entID from company_codes

UNION

select role_type AS ROLENAME, 'Roles' as entitlementtype, 'XYZ' as securitysystem,'XYZ' as endpoint,1 as status , id as entID from role_types]]>
</sql-query>
<mapper description="This is the mapping field for Saviynt Field name" entnotpresentaction="noaction" createentitlementtype="true">
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="securitysystem" type="character"></mapfield>
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpoint" type="character"></mapfield>
<mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="entitlementtype" type="character"></mapfield>
<mapfield saviyntproperty="entitlementvalues.entitlement_value" sourceproperty="ROLENAME" type="character"></mapfield>
<mapfield saviyntproperty="entitlementvalues.entitlementID" sourceproperty="entID" type="character"></mapfield>
<mapfield saviyntproperty="entitlementvalues.status" sourceproperty="status" type="character"></mapfield>
</mapper>
</dataMapping>

------------------------------------

Tried CAST() :

<dataMapping>
<sql-query description="This is the Source Database Query">
<![CDATA[
select CAST(company_code AS VARCHAR) AS ROLENAME,'Company_Code' as entitlementtype,'XYZ' as securitysystem,'XYZ' as endpoint,1 as status , company_code as entID from company_codes

UNION

select role_type AS ROLENAME,'Roles' as entitlementtype, 'XYZ' as securitysystem,'XYZ' as endpoint,1 as status , id as entID from role_types]]>
</sql-query>
<mapper description="This is the mapping field for Saviynt Field name" entnotpresentaction="noaction" createentitlementtype="true">
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="securitysystem" type="character"></mapfield>
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpoint" type="character"></mapfield>
<mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="entitlementtype" type="character"></mapfield>
<mapfield saviyntproperty="entitlementvalues.entitlement_value" sourceproperty="ROLENAME" type="character"></mapfield>
<mapfield saviyntproperty="entitlementvalues.entitlementID" sourceproperty="entID" type="character"></mapfield>
<mapfield saviyntproperty="entitlementvalues.status" sourceproperty="status" type="character"></mapfield>
</mapper>
</dataMapping>

Let me know if anyone have any idea.

Regards,

Gaurav

[NM]

@gaurav_wagh what error did you observe after using cast?

[gaurav_wagh]

Same issue.

Regards,

Gaurav