pears.GetPersonEmploymentHistory

Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.

Original SQL

CREATE FUNCTION "pears"."GetPersonEmploymentHistory"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN "InPersonID" CHAR(20),IN "Word" CHAR(3) ) 
RETURNS long VARCHAR
BEGIN
  DECLARE "EmploymentHistory" long VARCHAR;
  DECLARE "Tab" CHAR(1);
  DECLARE "NewLine" CHAR(2);
  DECLARE "LoopCounter" tinyint;
  -- Word does not like CR LF, just CR
  IF "Word" = 'Yes' THEN SET "NewLine" = "char"(13)
  ELSE SET "NewLine" = "char"(13)+"char"(10)
  END IF;
  SET "Tab" = "char"(9);
  SET "LoopCounter" = 0;
  SET "EmploymentHistory" = '';
  FOR "fetchfor" AS "fetchcursor" no scroll cursor FOR
    SELECT "dateformat"("Employment"."StartDate",'Mmm yyyy')+"Tab"+"dateformat"("Employment"."LeaveDate",'Mmm yyyy')
      +"Tab"+"Company"."Name"+"Tab"+"Employment"."Position" AS "NextLine"
      FROM "Employment" KEY JOIN "Company"
      WHERE "temp" = 0 AND "Employment"."StartDate" IS NOT NULL
      AND "Employment"."PersonID" = "InPersonID"
      ORDER BY "Employment"."StartDate" DESC FOR READ ONLY do
    IF "LoopCounter" > 0 THEN
      SET "EmploymentHistory" = "EmploymentHistory" || "NewLine"
    END IF;
    SET "EmploymentHistory" = "EmploymentHistory" || "NextLine";
    SET "LoopCounter" = "LoopCounter"+1 END FOR;
  RETURN("EmploymentHistory")
END