How to replace Databaseviews during Migration to Pega Cloud

General
, , ,
1

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?

2

Hello @GouthamiR4594,

Can you try to create this view in your cloud environment? I believe it should work. I was able to create one.

3

@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 ?

4

@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.

5

@GouthamiR4594

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.”

Modern and scalable reporting

dd.png
dd.png1825×903 340 KB

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.

:warning: 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