Return to your procedure called get_po_header_data
and modify it. Start by renaming the LOGINNAME
column of the output table to FULLNAME
. Also change the output length to 256. This is needed to match later on which the anticipated output structure.
Change the last SELECT statement. Remove the LOGINNAME
column from the field list and replace it with a call to the scalar function that you created earlier. Make sure to pass the NAME.FIRST
, NAME.MIDDLE
and NAME.LAST
name columns to the scalar function call.
The completed code should look very similar to this.
PROCEDURE "get_po_header_data" (
OUT EX_TOP_3_EMP_PO_COMBINED_CNT TABLE(
FULLNAME NVARCHAR(256),
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 "PO.Header" WHERE PURCHASEORDERID IN (
SELECT PURCHASEORDERID
FROM "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 "PO.Header" WHERE PURCHASEORDERID IN (
SELECT PURCHASEORDERID
FROM "PO.Item"
WHERE "PRODUCT.PRODUCTID" IS NOT NULL)
GROUP BY "HISTORY.CHANGEDBY.EMPLOYEEID";
EX_TOP_3_EMP_PO_COMBINED_CNT =
SELECT "get_full_name"( "NAME.FIRST", "NAME.MIDDLE", "NAME.LAST") as FULLNAME,
crcnt.CREATE_CNT, chcnt.CHANGE_CNT,
crcnt.CREATE_CNT + chcnt.CHANGE_CNT AS COMBINED_CNT
FROM "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