Question
ASB
IN
Last activity: 9 Feb 2024 7:07 EST
How to replace Databaseviews during Migration to Pega Cloud
We have a database view in system to fetch hierarchy of employees. This databae view is currenlty mapped to a class using DATA-ADMIN-DB-Table. We have report definition defined on this class and using that report definition widely in our applications. SQL QUERY USED for the DATABASE view.
CREATE VIEW DATA.EmpHierarchy AS
WITH emphierarchylist
(loginid,employeenumber,supervisoremployeenumb) as
(SELECT (loginid,employeenumber,supervisoremployeenumb FROM pr_ASB_StaffUp_Data_Employees
UNION ALL
SELECT eh.loginid,eh.employeenumber,et.supervisoremployeenumb FROM emphierarchylist eh
JOIN pr_ASB_StaffUp_Data_Employees et
on (eh.supervisoremployeenumb = et.employeenumber ))
SELECT emph.loginid,emph.employeenumber,emph.supervisoremployeenumb,
supd.loginid as "supervisorloginid" from emphierarchylist emph
LEFT JOIN pr_ASB_StaffUp_Data_Employees supd on
emph.supervisoremployeenumb = supd.employeenumber;
WE KNOW THAT database views will not be supported in Pega cloud. This DATABASE VIEW uses UNION in the query. How do we achieve this UNION with report definition in Pega cloud? we do not want to use CONNECT-SQL SINCE our organization doesn't encourage using Custom SQL. SO is there any way Pega report definition supports the generation of above query using UNION?
***Edited by Moderator: Marije to add Case tags***
Accepted Solution
Updated: 9 Feb 2024 7:07 EST
Pegasystems Inc.
GB
I can see the advice provided to you in closed support ticket INC-252842 ( Replacing DATABASE Views in Pega cloud) ·Closure note
"I have further checked with team regarding running the query and there is no such capability to do so other than connect-sql."
In Pega Cloud, there is no direct way to generate a SQL query using UNION in a report definition without using CONNECT-SQL. However, you can achieve a similar result by using Aggregate Sources within Data pages. This functionality allows UNION of disparate data sets (which can be in the form of Report result or Connector response), and merge them directly as a resultset within the Data Page response. This solution is scalable and does not require direct database access.
⚠ This is a GenAI-powered tool. All generated answers require validation against the provided references.
How to perform UNION of different data sets within PEGA
I can see the advice provided to you in closed support ticket INC-252842 ( Replacing DATABASE Views in Pega cloud) ·Closure note
"I have further checked with team regarding running the query and there is no such capability to do so other than connect-sql."
In Pega Cloud, there is no direct way to generate a SQL query using UNION in a report definition without using CONNECT-SQL. However, you can achieve a similar result by using Aggregate Sources within Data pages. This functionality allows UNION of disparate data sets (which can be in the form of Report result or Connector response), and merge them directly as a resultset within the Data Page response. This solution is scalable and does not require direct database access.
⚠ This is a GenAI-powered tool. All generated answers require validation against the provided references.
How to perform UNION of different data sets within PEGA
Pega Platform database tools > SQL query management
Maantic
IN
Hello @GouthamiR4594,
Can you try to create this view in your cloud environment? I believe it should work. I was able to create one.
ASB
IN
@GitarthaD33 Pega is suggesting that Database views are not supported in Pega Cloud environment.
That is why we are looking for other alternative options.
have you created database views on Pega cloud ?
Accepted Solution
Updated: 9 Feb 2024 7:07 EST
Pegasystems Inc.
GB
I can see the advice provided to you in closed support ticket INC-252842 ( Replacing DATABASE Views in Pega cloud) ·Closure note
"I have further checked with team regarding running the query and there is no such capability to do so other than connect-sql."
In Pega Cloud, there is no direct way to generate a SQL query using UNION in a report definition without using CONNECT-SQL. However, you can achieve a similar result by using Aggregate Sources within Data pages. This functionality allows UNION of disparate data sets (which can be in the form of Report result or Connector response), and merge them directly as a resultset within the Data Page response. This solution is scalable and does not require direct database access.
⚠ This is a GenAI-powered tool. All generated answers require validation against the provided references.
How to perform UNION of different data sets within PEGA
I can see the advice provided to you in closed support ticket INC-252842 ( Replacing DATABASE Views in Pega cloud) ·Closure note
"I have further checked with team regarding running the query and there is no such capability to do so other than connect-sql."
In Pega Cloud, there is no direct way to generate a SQL query using UNION in a report definition without using CONNECT-SQL. However, you can achieve a similar result by using Aggregate Sources within Data pages. This functionality allows UNION of disparate data sets (which can be in the form of Report result or Connector response), and merge them directly as a resultset within the Data Page response. This solution is scalable and does not require direct database access.
⚠ This is a GenAI-powered tool. All generated answers require validation against the provided references.
How to perform UNION of different data sets within PEGA
Pega Platform database tools > SQL query management
J.P. Morgan Services India PVT Ltd
IN
@GitarthaD33 Were you able to create database view on Pega cloud? Is it possible? How could you able to do it? Please share your inputs.