Most Active developer
Hi can you please suggest a simplest way to get the most active developer .
I am joining Data-rule-summary with Rule-History and getting number of actions grouped by developer. Now i have to apply a MAX on counts and then find which all operators' count matches max count. How we achieve it using report definitions.
I am trying to do a equivalent of below sql query using report definition and subreports.
with TEMP as (select V.pxUpdateOperator operator,count(H.pzINskey) CountUpdate from mnprule.pr4_rule_vw V, MNPDATA.pr4_history_rule H where V.pzinskey=H.PXHISTORYFORREFERENCE
and V.pxupdatedatetime is not null
and V.pxupdatedatetime > sysdate -7
and V.pyclassname is not null
group by V.pxUpdateOperator)
select * from TEMP where CountUpdate = (select Max (TEMP.CountUpdate) from TEMP)
Pegasystems Inc.
US
What you are wanting to do sounds either recursive or circular. You are wanting to filter on a value that is not known until the main Report Definition runs its course, including a descending sort on COUNT().
You could call the Report Definition from a Data Page then have the Data Page post-process the result set throwing away every row that does not match the COUNT() in the first row.
Why does your query start with "with" BTW? That seems odd. Have seen hierarchical SQL use "START WITH"...
Cognizant Technology Solutions
US
@PEDEL my query was for representation purpose only. With clause sometime helps in refactoring subqueries.
as per oracle website "The WITH clause, or subquery factoring clause, is part of the SQL-99 standard and was added into the Oracle SQL syntax in Oracle 9.2. The WITH clause may be processed as an inline view or resolved as a temporary table. The advantage of the latter is that repeated references to the subquery may be more efficient as the data is easily retrieved from the temporary table, rather than being requeried by each reference. You should assess the performance implications of the WITH clause on a case-by-case basis."
Coming to report, I was just exploring the capability for reporting and how something can be achieved easily. From PEGA we see limitation that we can not have sub-report inside sub-report. We can do post processing of data page but want to see is this report is possible using just reports.
if pega had subreport inside subreport, we could have SUB1 as count group by operator, another SUB2 on top of SUB1 to get MAX(single row, single column) and then third.the main report on top of SUB2 which would be similar to SUB1 but filter criteria on SUB2.MAXCOUNT. But sounds like lots of work.
Another option can be function alias using partition by clause. Need to explore this option.
Pegasystems Inc.
US
Ok. I had always assumed database vendors "magically" refactored correlated subqueries to have them not execute the subquery for every row. Pega does not support WITH but it does support generating correlated subqueries.
Pega does not support a subreport calling a subreport as you noted.
I did not mention analytic functions (partition by) either because Pega does not support them.
Pega is not meant for performing heavy-duty Business Intelligence reporting against the same database used by the Web Tier. This is why the LSA course mentions BIX, ETL, Data Warehousing, Archiving and Purging..