Create Scalar User Defined Functions

Enter the code into the editor as shown here. Please note the default for parameter im_employeeid which makes assigning a value to the parameter optional.

FUNCTION "get_full_name" (
              IN im_firstname NVARCHAR(40) ,
              IN im_middlename NVARCHAR(40),
              IN im_lastname NVARCHAR(40),
              IN im_employeeid NVARCHAR(10) DEFAULT '' )
    			RETURNS ex_fullname NVARCHAR(256) AS
BEGIN

  if :im_middlename IS NULL THEN
     ex_fullname = :im_lastname || ', ' || :im_firstname;
  ELSE
     ex_fullname = :im_lastname || ', ' ||
                   :im_firstname || ' ' || :im_middlename;
  END IF;

  IF :im_employeeid <> '' then
     ex_fullname = :ex_fullname || '(' || :im_employeeid || ')';
  END IF;
END;

Save the function.

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 NAMEFIRST, NAMEMIDDLE and NAMELAST 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, "CREATEDBY" as EID
     FROM "OPENSAP_PURCHASEORDER_HEADERS" WHERE ID IN (
                     SELECT "POHEADER_ID"
                          FROM "OPENSAP_PURCHASEORDER_ITEMS"
          WHERE "PRODUCT_PRODUCTID" IS NOT NULL)
            GROUP BY  "CREATEDBY";

po_change_cnt = SELECT COUNT(*) AS CHANGE_CNT, "MODIFIEDBY" as EID
     FROM "OPENSAP_PURCHASEORDER_HEADERS"  WHERE ID IN (
                     SELECT "POHEADER_ID"
                          FROM "OPENSAP_PURCHASEORDER_ITEMS"
          WHERE "PRODUCT_PRODUCTID" IS NOT NULL)
             GROUP BY  "MODIFIEDBY";

EX_TOP_3_EMP_PO_COMBINED_CNT =
        SELECT "get_full_name"( "NAMEFIRST", "NAMEMIDDLE", "NAMELAST") as FULLNAME,
         crcnt.CREATE_CNT, chcnt.CHANGE_CNT,  crcnt.CREATE_CNT +
         chcnt.CHANGE_CNT AS COMBINED_CNT
            FROM "OPENSAP_MD_EMPLOYEES" as emp
            LEFT OUTER JOIN :PO_CREATE_CNT AS crcnt
             ON emp.email = crcnt.EID
           LEFT OUTER JOIN :PO_CHANGE_CNT AS chcnt
           ON emp.email = chcnt.EID
          ORDER BY COMBINED_CNT DESC LIMIT 3;
END

Save the procedure.