
CREATE VIEW [dbo].[SPICE_Occurence_VIEW]
AS
SELECT     dbo.PTaxonGeoTDWG.id, dbo.PTaxon.RIdentifier as TaxonId, dbo.tblLevel3.[L3 area] + ', ' + dbo.tblLevel2.[L2 region] + ', ' + dbo.tblLevel1.[L1 continent] AS placename, 
                      dbo.f_species2000_getOccurence(dbo.PTaxonGeoTDWG.id) AS Occurence
FROM         dbo.PTaxonGeoTDWG INNER JOIN
                      dbo.tblLevel3 ON dbo.PTaxonGeoTDWG.l3_code = dbo.tblLevel3.[L3 code] INNER JOIN
                      dbo.tblLevel2 ON dbo.tblLevel3.[L2 code] = dbo.tblLevel2.[L2 code] INNER JOIN
                      dbo.tblLevel1 ON dbo.tblLevel2.[L1 code] = dbo.tblLevel1.[L1 code] INNER JOIN
                      dbo.PTaxon ON dbo.PTaxonGeoTDWG.PTNameFk = dbo.PTaxon.PTNameFk AND dbo.PTaxonGeoTDWG.PTRefFk = dbo.PTaxon.PTRefFk



GO
