The completed code should be very similar to this. If you do not wish to type this code, you can reference the solution web page at http://<hostname>:51013/workshop/admin/ui/exerciseMaster/?workshop=dev602&sub=ex2_12
PROCEDURE "dev602.procedures::get_po_header_data" (
OUT EX_TOP_3_EMP_PO_COMBINED_CNT TABLE(
LOGINNAME NVARCHAR(12),
CREATE_CNT INTEGER,
CHANGE_CNT INTEGER,
COMBINED_CNT INTEGER ) )
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
--DEFAULT SCHEMA <default_schema_name>
READS SQL DATA AS
BEGIN
po_create_cnt = SELECT COUNT(*) AS CREATE_CNT, "HISTORY.CREATEDBY.EMPLOYEEID" AS EID
FROM "dev602.data::PO.Header" WHERE PURCHASEORDERID IN (
SELECT PURCHASEORDERID
FROM "dev602.data::PO.Item"
WHERE "PRODUCT.PRODUCTID" IS NOT NULL)
GROUP BY "HISTORY.CREATEDBY.EMPLOYEEID";
po_change_cnt = SELECT COUNT(*) AS CHANGE_CNT, "HISTORY.CHANGEDBY.EMPLOYEEID" AS EID
FROM "dev602.data::PO.Header" WHERE PURCHASEORDERID IN (
SELECT PURCHASEORDERID
FROM "dev602.data::PO.Item"
WHERE "PRODUCT.PRODUCTID" IS NOT NULL)
GROUP BY "HISTORY.CHANGEDBY.EMPLOYEEID";
EX_TOP_3_EMP_PO_COMBINED_CNT =
SELECT emp.LOGINNAME, crcnt.CREATE_CNT, chcnt.CHANGE_CNT, crcnt.CREATE_CNT +
chcnt.CHANGE_CNT AS COMBINED_CNT
FROM "dev602.data::MD.Employees" as emp
LEFT OUTER JOIN :PO_CREATE_CNT AS crcnt
ON emp.EMPLOYEEID = crcnt.EID
LEFT OUTER JOIN :PO_CHANGE_CNT AS chcnt
ON emp.EMPLOYEEID = chcnt.EID
ORDER BY COMBINED_CNT DESC LIMIT 3;
END