pears.ContactEventDepartmentView

Gives DepartmentID linked to a Contact Event record

CREATE VIEW "pears"."ContactEventDepartmentView"( "contacteventid",
  "departmentid","contactdate","staffid","classcode" ) AS
  SELECT DISTINCT "c"."contacteventid","p"."departmentid","c"."contactdate","c"."staffid","c"."classcode"
    FROM "pears"."contactevent" AS "c" KEY JOIN("pears"."placement" AS "p") WHERE "c"."placementid" IS NULL UNION ALL
  SELECT DISTINCT "c"."contacteventid","v"."departmentid","c"."contactdate","c"."staffid","c"."classcode"
    FROM "pears"."contactevent" AS "c" KEY JOIN("pears"."vacancy" AS "v") WHERE "c"."placementid" IS NULL UNION ALL
  SELECT DISTINCT "c"."contacteventid","v"."departmentid","c"."contactdate","c"."staffid","c"."classcode"
    FROM "pears"."contactevent" AS "c" KEY JOIN("pears"."progress" AS "p"),"pears"."vacancy" AS "v" WHERE "p"."vacancyid" = "v"."vacancyid" AND "c"."placementid" IS NULL AND "c"."vacancyid" IS NULL UNION ALL
  SELECT DISTINCT "c"."contacteventid","s"."defaultdepartid" AS "departmentid","c"."contactdate","c"."staffid","c"."classcode"
    FROM "pears"."contactevent" AS "c" KEY JOIN("pears"."staff" AS "s") WHERE "c"."placementid" IS NULL AND "c"."vacancyid" IS NULL AND "c"."progressid" IS NULL
GO
 
COMMENT ON VIEW "pears"."ContactEventDepartmentView" IS 
	'Gives DepartmentID linked to a Contact Event record'
GO
 
COMMENT TO PRESERVE FORMAT ON VIEW "pears"."ContactEventDepartmentView" IS 
{CREATE VIEW ContactEventDepartmentView 
 (contacteventid, departmentid, contactdate, staffid, classcode) 
 AS SELECT DISTINCT c.contacteventid, p.departmentid, c.contactdate, c.staffid, c.classcode 
 FROM contactevent c KEY JOIN ( placement p) WHERE NOT c.placementid IS NOT NULL     
 UNION ALL SELECT DISTINCT c.contacteventid,v.departmentid, c.contactdate, c.staffid,  c.classcode 
 FROM contactevent c KEY JOIN ( vacancy v) WHERE c.placementid IS NULL 
 UNION ALL  SELECT DISTINCT c.contacteventid, v.departmentid, c.contactdate, c.staffid,  c.classcode  
 FROM contactevent c KEY JOIN (progress p),vacancy v WHERE p.vacancyid=v.vacancyid AND  c.placementid IS NULL AND  c.vacancyid IS NULL 
 UNION ALL  SELECT DISTINCT c.contacteventid, s.defaultdepartid AS departmentid, c.contactdate, c.staffid, c.classcode 
 FROM contactevent c KEY JOIN ( staff s) WHERE c.placementid IS  NULL AND  c.vacancyid IS  NULL AND  c.progressid IS NULL
}