Replicate SQL with sub-queries inside PRPC
Hi,
I'm trying to replicate this SQL in PRPC:
SELECT T1.pxassignedoperatorid,
T1.TOTAL,
T2.SLADEADLINE
FROM
(SELECT pxassignedoperatorid,
COUNT (*) AS TOTAL
FROM pc_assign_workbasket
GROUP BY pxassignedoperatorid
) T1
LEFT OUTER JOIN
(SELECT pxassignedoperatorid,
COUNT (*) AS SLADEADLINE
FROM pc_assign_workbasket
WHERE pxdeadlinetime IS NOT NULL
GROUP BY pxassignedoperatorid
) T2
ON T1.pxassignedoperatorid = T2.pxassignedoperatorid
ORDER BY T1.pxassignedoperatorid
To get a table like this:
PXASSIGNEDOPERATORID | TOTAL | SLADEADLINE
WorkBasket1 | 18 | 3
WorkBasket2 | 688 | (null)
WorkBasket3 | 1029 | 66
The above works in Oracle's SQL Developer. But I can't seem to find a solution in PRPC using Report Definitions... I tried using two Sub-Reports (one for each query in the parenthesis) inside a Report Definition, but I can't configure the "left outer join".
Any help is appreciated.
Thanks,
Jose
Coforge
GB
Hi,
I would suggest you to create a View, that contains union of the 2 table results (your SQL Query) in the database schema and then Map the Report Definition to the View class
SoftServe
US
Thanks RajaJanga,
I never used Views before, but at the end I solve it using two report definitions (one of them a subreport). The equivalente query changed to this: (notice I used just one subquery ─one subreport─ instead of two)
SELECT T1.pxassignedoperatorid, COUNT(*) AS TOTAL, T2.SLADEADLINE
FROM
pc_assign_workbasket T1
LEFT OUTER JOIN
(SELECT pxassignedoperatorid,
COUNT (*) AS SLADEADLINE
FROM pc_assign_workbasket
WHERE pxdeadlinetime IS NOT NULL
GROUP BY pxassignedoperatorid
) T2
ON T1.pxassignedoperatorid = T2.pxassignedoperatorid
GROUP BY T1.pxassignedoperatorid, T2.SLADEADLINE
To do the "left join", in the configure sub-report dialog, choose: Include all rows in report "NameOfYourMainReportHere". In the main report, I set 3 columns:
- WorkBasket (pxAssignedOperatorID)
- Total, that uses the summary function COUNT
- Cases Out of SLA Deadline, that uses the Subreport column
Notice the subreport returns two columns: WorkBasket and Cases Out of SLA Deadline, from which I choose WorkBasket to join with the main report.
Hopefully this can help somebody that encounters a similar requirement.
Best regards,
Jose
Coforge
GB
That's great Jose,
I would suggest you to use views approach, because if you are running two report definations - that means you are quering DB twise, so it will impact the Performance. But if you follow Views approach, it is single Reprot Definations and you can impove performance as well.
Regards,
Raja J