pears.DBCleanupOrphanTagValue

CREATE FUNCTION "pears"."DBCleanupOrphanTagValue"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN "countordo" CHAR(1) ) 
RETURNS INTEGER
BEGIN
  DECLARE @RC INTEGER;
  SET @RC = 0;
  IF "countordo" = 'C' THEN
    SELECT "count"() INTO @RC FROM "tagvalue"
      WHERE(((("taglocation" = 'P') OR("taglocation" LIKE 'A%')) AND NOT EXISTS(SELECT * FROM "person" WHERE "personid" = "tagvalue"."id"))
      OR(("taglocation" = 'E') AND NOT EXISTS(SELECT * FROM "employment" WHERE "employmentid" = "tagvalue"."id"))
      OR(("taglocation" = 'C') AND NOT EXISTS(SELECT * FROM "company" WHERE "companyid" = "tagvalue"."id"))
      OR(("taglocation" LIKE 'V%') AND NOT EXISTS(SELECT * FROM "vacancy" WHERE "vacancyid" = "tagvalue"."id"))
      OR(("taglocation" LIKE 'L%') AND NOT EXISTS(SELECT * FROM "placement" WHERE "placementid" = "tagvalue"."id"))
      OR(("taglocation" = 'U') AND NOT EXISTS(SELECT * FROM "staff" WHERE "staffid" = "tagvalue"."id"))
      OR(("taglocation" = 'N') AND NOT EXISTS(SELECT * FROM "contactevent" WHERE "contacteventid" = "tagvalue"."id")));
    RETURN @RC
  END IF;
  DELETE FROM "tagvalue" WHERE(("taglocation" = 'P') OR("taglocation" LIKE 'A%'))
    AND NOT EXISTS(SELECT * FROM "person" WHERE "personid" = "tagvalue"."id");
  SET @RC = @RC+(SELECT @@rowcount);
  DELETE FROM "tagvalue" WHERE("taglocation" = 'C')
    AND NOT EXISTS(SELECT * FROM "company" WHERE "companyid" = "tagvalue"."id");
  DELETE FROM "tagvalue" WHERE("taglocation" = 'E')
    AND NOT EXISTS(SELECT * FROM "employment" WHERE "employmentid" = "tagvalue"."id");
  SET @RC = @RC+(SELECT @@rowcount);
  DELETE FROM "tagvalue" WHERE("taglocation" LIKE 'V%')
    AND NOT EXISTS(SELECT * FROM "vacancy" WHERE "vacancyid" = "tagvalue"."id");
  SET @RC = @RC+(SELECT @@rowcount);
  DELETE FROM "tagvalue" WHERE("taglocation" LIKE 'L%')
    AND NOT EXISTS(SELECT * FROM "placement" WHERE "placementid" = "tagvalue"."id");
  SET @RC = @RC+(SELECT @@rowcount);
  DELETE FROM "tagvalue" WHERE("taglocation" = 'U')
    AND NOT EXISTS(SELECT * FROM "staff" WHERE "staffid" = "tagvalue"."id");
  SET @RC = @RC+(SELECT @@rowcount);
  DELETE FROM "tagvalue" WHERE("taglocation" = 'N')
    AND NOT EXISTS(SELECT * FROM "contactevent" WHERE "contacteventid" = "tagvalue"."id");
  SET @RC = @RC+(SELECT @@rowcount);
  CALL "AuditLog"('CLEANUP',NULL,"string"(@RC,' Unattached Question Answers deleted '),NULL,NULL);
  RETURN @RC
END