CREATE OR REPLACE FORCE VIEW "PIRSA"."ER_TEST" ("SITE_ID", "LOCATION") AS 
SELECT
  s.site_no AS SITE_ID,
  s.geodetic_point AS LOCATION
FROM SITE s
  LEFT OUTER JOIN DD_SVY_METHOD svym ON svym.svy_method_code = s.svy_method_horiz_code
  LEFT OUTER JOIN MD_MINERAL_DEPOSIT md ON md.site_no = s.site_no
  LEFT OUTER JOIN MD_MINERALISED_ZONE mz ON mz.site_no = s.site_no
WHERE
  COALESCE(md.mineral_deposit_no, mz.zone_no) IS NOT NULL
  AND s.geodetic_point is not null;