pears.GetQuestTextLong [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.GetQuestTextLong ======
<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"."GetQuestTextLong"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in "tagloc" char(3),in "wildtagid" char(3),in "parentid" char(20) ) 
returns long varchar
begin
  declare "rv" long varchar;
  declare "tagval" double;
  declare "textval" long varchar;
  declare "x" char(4);
  declare "y" integer;
  declare "xunits" char(11);
  declare "xdesc" char(25);
  declare "i" smallint;
  declare "tab" char(1);
  declare "newline" char(2);
  set "tab" = "char"(9);
  set "newline" = "char"(13)+"char"(10);
  set "rv" = '';
  for "fetchfor" as "fetchcursor" no scroll cursor for
    select "tagid" as "toptagid",(if "isnull"("longdescription",'') = '' then "description" else "longdescription" endif) as "tagdesc","tagtype","units" from "tag" where "taglocation" = "tagloc" and "tagid" like "wildtagid" order by "sortorder" asc for read only do
    set "xunits" = "isnull"("units",'');
    if "xunits" <> '' then
      set "xunits" = ' '+"xunits"
    end if;
    set "rv" = "rv"+"tagdesc"+"tab";
    case "tagtype" when 'N' then
      select "max"("value") into "tagval" from "tagvalue" where "taglocation" = "tagloc" and "id" = "parentid" and "tagid" = "toptagid";
      if "isnull"("tagval",0) <> 0 then
        set "rv" = "rv"+"string"("tagval")+"xunits"
      end if when 'D' then
      select "max"("value") into "tagval" from "tagvalue" where "taglocation" = "tagloc" and "id" = "parentid" and "tagid" = "toptagid";
      if "isnull"("tagval",0) <> 0 then
        set "y" = "tagval"; //// Following expression does not behave with a double
        set "rv" = "rv" || "dateformat"("date"('1899-12-30')+"y",'dd/mm/yyyy')
      end if when 'T' then
      select "max"("textvalue") into "textval" from "tagvalue" where "taglocation" = "tagloc" and "id" = "parentid" and "tagid" = "toptagid";
      if "textval" is not null then
        set "rv" = "rv"+"textval"
      end if when 'U' then
      select "max"("textvalue") into "textval" from "tagvalue" where "taglocation" = "tagloc" and "id" = "parentid" and "tagid" = "toptagid";
      if "textval" is not null then
        set "rv" = "rv"+"textval"
      end if when 'M' then
      select "max"("tagchoiceid") into "x" from "tagvalue" where "taglocation" = "tagloc" and "id" = "parentid" and "tagid" = "toptagid";
      if "x" is null then
        set "x" = '_'
      end if;
      set "rv" = "rv"+"isnull"((select(if "isnull"("longdescription",'') = '' then "description" else "longdescription" endif) from "tagchoice" where "taglocation" = "tagloc" and "tagid" = "toptagid" and "tagchoiceid" = "x"),'')
    else
      set "i" = 0;
      for "fetch2for" as "fetch2cursor" no scroll cursor for
        select(if "isnull"("tagchoice"."longdescription",'') = '' then "tagchoice"."description" else "tagchoice"."longdescription" endif) as "tagchdesc","tagvalue"."tagchoiceid","tagvalue"."value" as "tgval","tagchoice"."tagchoiceparentid" as "tcpid"
          from "tagvalue" key join "tagchoice"
          where "tagvalue"."taglocation" = "tagloc" and "tagvalue"."tagid" = "toptagid" and "tagvalue"."id" = "parentid" order by "tagchoice"."sortorder" asc for read only do
        if "i" > 0 then
          set "rv" = "rv"+', '
        end if;
        case "tagtype" when 'L' then
          set "rv" = "rv"+"tagchdesc";
          if "tagchoiceid" = '*' then
            set "rv" = "rv"+' '+"string"("tgval")
          end if when 'S' then
          set "rv" = "rv"+"tagchdesc"+' '+"string"("tgval")+"xunits" when 'G' then
          select "max"((if "isnull"("longdescription",'') = '' then "description" else "longdescription" endif)) into "xdesc" from "tagchoice" where "taglocation" = "tagloc" and "tagid" = "toptagid" and "value" = "tgval" and "subchoice" = 1;
          if "isnull"("xdesc",'') <> '' then
            set "rv" = "rv"+"tagchdesc"+' '+"xdesc"
          end if when 'Q' then
          select "max"((if "isnull"("longdescription",'') = '' then "description" else "longdescription" endif)) into "xdesc" from "tagchoice" where "taglocation" = "tagloc" and "tagid" = "toptagid" and "tagchoiceid" = "tcpid" and "subchoice" = 0;
          if "isnull"("xdesc",'') <> '' then
            set "rv" = "rv"+"xdesc"+' '+"tagchdesc"
          end if
        end case;
        set "i" = "i"+1
      end for
    end case;
    set "rv" = "rv"+"newline" end for;
  return("rv")
end
</code>