pears.placementelementvalueV2

CREATE FUNCTION "pears"."placementelementvalueV2"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN "splacid" CHAR(20),IN "spersid" CHAR(20),IN "svacid" CHAR(20),IN "selementname" CHAR(20) ) 
RETURNS long VARCHAR
BEGIN
  DECLARE "rv" long VARCHAR;
  DECLARE "l" long VARCHAR;
  DECLARE "n" REAL;
  DECLARE "i" INTEGER;
  DECLARE "dt" CHAR(1);
  DECLARE "subname" CHAR(30);
  DECLARE "subchar" CHAR(1);
  DECLARE "tempid" CHAR(20);
  IF "selementname" = 'PLACEMENTID' THEN
    RETURN(CAST("splacid" AS long VARCHAR))
  ELSEIF "selementname" = 'PERSONID' THEN
    RETURN(CAST("spersid" AS long VARCHAR))
  ELSEIF "selementname" = 'VACANCYID' THEN
    RETURN(CAST("svacid" AS long VARCHAR))
  ELSEIF "selementname" = 'SALARY' THEN
    SET "n" = (SELECT "salary" FROM "placement" WHERE "placementid" = "splacid");
    RETURN(CAST("n" AS long VARCHAR))
  ELSE
    SET "l" = NULL;
    SET "tempid" = NULL;
    SELECT "placementelement"."value","placementelement"."placementid" INTO "l",
      "tempid" FROM "placementelement" KEY JOIN "placementelementtype"
      WHERE "placementelement"."placementid" = "splacid" AND "placementelementtype"."name" = "selementname";
    IF "tempid" IS NOT NULL THEN
      RETURN("l")
    END IF;
    SELECT "defaultvalue" INTO "l" FROM "placementelementtype" WHERE "name" = "selementname";
    IF "l" IS NULL THEN
      RETURN(NULL)
    END IF;
    "subloop": loop
      SET "i" = "patindex"('%{%}%',"l");
      IF "i" = 0 THEN
        leave "subloop"
      END IF;
      SET "subname" = '';
      "charloop": loop
        SET "subchar" = "substr"("l","i",1);
        IF "length"("l") = 1 THEN
          SET "l" = '' // IN this CASE stuffing crashes the server
        ELSE
          SET "l" = "stuff"("l","i",1,'')
        END IF;
        IF "subchar" = '}' OR "i" > "length"("l") THEN // The latter IS precautionary
          leave "charloop"
        ELSEIF "subchar" <> '{' THEN
          SET "subname" = "subname" || "subchar"
        END IF
      END loop "charloop";
      SET "rv" = "placementelementvalueV2"("splacid","spersid","svacid","subname");
      IF "subname" = 'PLACEMENTID' OR "subname" = 'PERSONID' OR "subname" = 'VACANCYID' THEN
        SET "dt" = 'S'
      ELSE
        SET "dt" = NULL;
        SELECT "datatype" INTO "dt" FROM "placementelementtype" WHERE "name" = "subname"
      END IF;
      IF "rv" IS NULL THEN
        SET "rv" = 'null'
      ELSE
        IF "dt" = 'S' THEN
          SET "rv" = '''' || "rv" || ''''
        ELSE
          SET "rv" = 'cast(' || "rv" || ' as real)'
        END IF END IF;
      SET "l" = "insertstr"("i"-1,"l","rv")
    END loop "subloop";
    IF "l" IS NULL OR "length"("l") = 0 THEN
      RETURN(NULL)
    END IF;
    SET "rv" = NULL;
    SET "l" = 'set rv=(select ' || "l" || ')';
    EXECUTE IMMEDIATE "l";
    RETURN("rv")
  END IF
END