====== pears.placementelementvalueV2 ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
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
</code>