pears.placementelementvalueV2

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."placementelementvalueV2" IS 
'create function 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'