pears.StaffInfo

View - Adds Branch - Division etc information to StaffID and name.

CREATE VIEW "pears"."StaffInfo"( "staffid",
  "name","title","shortid","tempdesk","division","agency","department" ) 
  AS SELECT "staff"."staffid","staff"."name","staff"."title","staff"."shortid",
    (SELECT "tempdesk"."name" FROM "pears"."tempdesk" WHERE "staff"."tempdeskid" = "tempdesk"."tempdeskid"),
    (SELECT "division"."name" FROM "pears"."division" WHERE "staff"."divisionid" = "division"."divisionid"),
    (SELECT "agencydetails"."name" FROM "pears"."agencydetails" WHERE "staff"."agencyid" = "agencydetails"."agencyid"),
    (SELECT "department"."name" FROM "pears"."department" WHERE "staff"."defaultdepartid" = "department"."departmentid")
    FROM "pears"."staff"
GO
 
COMMENT ON VIEW "pears"."StaffInfo" IS 
	'View - Adds Branch - Division etc information to StaffID and name.'
GO
 
COMMENT TO PRESERVE FORMAT ON VIEW "pears"."StaffInfo" IS 
{CREATE VIEW StaffInfo 
(staffid,"name",title,shortid,tempdesk,division,agency,department)
AS SELECT staffid,"name",title,shortid,
(SELECT "name" FROM pears.tempdesk WHERE staff.tempdeskid=tempdesk.tempdeskid),
(SELECT "name" FROM pears.division WHERE staff.divisionid=division.divisionid),
(SELECT "name" FROM pears.agencydetails WHERE staff.agencyid=agencydetails.agencyid),
(SELECT "name" FROM pears.department WHERE staff.defaultdepartid=department.departmentid)
FROM pears.staff
}