Can someone please help to convert Sql query to HQL. Below is the sql Query. I want to use this to custom report
select i.upi,i.account_name,b.display_name as [Business Role Name],
(select name from identityiq.spt_bundle where id =br.child) AS [IT Role Name]
from identityiq.spt_identity_entitlement ie
LEFT JOIN identityiq.spt_bundle b on ie.value = b.name
LEFT JOIN identityiq.spt_bundle_requirements br on b.id=br.bundle
JOIN identityiq.spt_identity i on ie.identity_id = i.id
where b.name ='Test`Preformatted text`';
Hi @poison001 ,
SELECT i.upi, i.accountName, b.displayName AS businessRoleName,
(SELECT childBundle.name FROM Bundle childBundle WHERE childBundle.id = br.child.id) AS itRoleName
FROM IdentityEntitlement ie
LEFT JOIN ie.bundle b
LEFT JOIN b.bundleRequirements br
JOIN ie.identity i
WHERE b.name = ‘TestPreformatted text’
select i.upi,i.account_name,b.display_name as [Business Role Name]
from identityiq.spt_identity_entitlement ie
LEFT JOIN identityiq.spt_bundle b on ie.value = b.name
LEFT JOIN identityiq.spt_bundle_requirements br on b.id=br.bundle
JOIN identityiq.spt_identity i on ie.identity_id = i.id
where b.name=‘Test’;
select i.upi,i.account_name,b.display_name as [Business Role Name]
from identityiq.spt_identity_entitlement ie
LEFT JOIN identityiq.spt_bundle b on ie.value = b.name
LEFT JOIN identityiq.spt_bundle_requirements br on b.id=br.bundle
JOIN identityiq.spt_identity i on ie.identity_id = i.id
where b.name='Test';
<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE TaskDefinition PUBLIC "sailpoint.dtd" "sailpoint.dtd">
<TaskDefinition executor="sailpoint.reporting.LiveReportExecutor" name="Identity Status Selected by Application" progressMode="Percentage" resultAction="Delete" subType="Extended Reports" template="true" type="LiveReport">
<Attributes>
<Map>
<entry key="report">
<value>
<LiveReport title="Displays information about access request with its approvals information based on application, date range or list of identities.">
<DataSource type="Hql">
<Query>
from sailpoint.object.IdentityEntitlement ie
LEFT JOIN sailpoint.object.Bundle b on ie.value = b.name
LEFT JOIN sailpoint.object.BundleRequirements br on b.id=br.bundle
JOIN sailpoint.object.Identity i on ie.identity_id = i.id
where b.name='Test'</Query>
</DataSource>
<Columns>
<ReportColumnConfig field="display_name" header="Business Role" property="b.displayName" sortable="true" width="110"/>
</Columns>
</LiveReport>
</value>
</entry>
</Map>
</Attributes>
<Description>Displays all roles for the chosen application(s).</Description>
<RequiredRights>
<Reference class="sailpoint.object.SPRight" name="FullAccessBusinessRoleReport"/>
</RequiredRights>
<Signature>
<Inputs>
<Argument multi="true" name="applications" required="true" type="Application">
<Description>Select the Logical Business application name</Description>
</Argument>
</Inputs>
</Signature>
</TaskDefinition>