pears.DAXTRADeDuplicate

CREATE FUNCTION "pears"."DAXTRADeDuplicate"( IN "paramsp" CHAR(20) ) 
RETURNS long VARCHAR
BEGIN
  DECLARE "RVal" long VARCHAR;
  DECLARE "fname" CHAR(100);
  DECLARE "lname" CHAR(100);
  DECLARE "filter" long VARCHAR;
  DECLARE "sel" long VARCHAR;
  DECLARE "maxrecs" INTEGER;
  SET "maxrecs" = 5;
  SET "filter" = ' where surname = lname';
  SELECT "dvalue" INTO "lname" FROM "DAXTRAParams" WHERE "ID" = "paramsp" AND "dname" = 'LASTNAME';
  IF(SELECT "count"() FROM "person" WHERE "surname" = "lname") > 5 THEN
    SELECT "dvalue" INTO "fname" FROM "DAXTRAParams" WHERE "ID" = "paramsp" AND "dname" = 'FIRSTNAME';
    SET "filter" = "string"("filter",' and substring(forenames,1,locate(forenames||'' '','' '')-1) = fname');
    IF(SELECT "count"() FROM "person" WHERE "surname" = "lname" AND "substring"("forenames",1,"locate"("forenames" || ' ',' ')-1) = "fname") > 5 THEN
      -- poss more filtering
    END IF END IF;
  SET "sel" = "string"('select list(string(personid,''~'',name,''~'',surname, ''<p>''),"char"(13)+"char"(10)) into RVal from person',"filter");
  EXECUTE IMMEDIATE "sel";
  RETURN "RVal"
END