1 (edited by RobLarge 13-02-2015 13:14:40)

Topic: Filtering bat records

This is probably a question for Mike, but if anyone else has something to contribute I'm all ears.

We have an xml report which we use very extensively which is a very heavily modified version of the supplied samples in a polygon report. One of my users has requested a modification to this report & I can't quite see how to go about it.

At the moment the report features a dropdown allowing the user to select whether to return 1. all records or 2. Only records of notable taxa (as referenced by a Designation set containing things we consider to be notable designations).

I have been asked to add a third option, to only return records of any bat taxa.

My preferred way of doing this would be to create a user function which would return true or false for a given Taxon_List_item_key. I envisage something like

ufn_Taxon_Is_A_Bat(TLIK), returning true if the TLIK is somewhere below Chiroptera in the hierarchy (including Chiroptera itself).

My understanding of the taxon dictionary is not sufficient to allow me to compose the SQL for this one, so any help would be very useful. All I need is a Select statement which returns all TLIK downstream of chiroptera, I can do the rest.

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

2

Re: Filtering bat records

Incidentally, in a previous attempt to do something like this I filtered using the INDEX_TAXON_NAME.SORT_ORDER field to exclude non-bats & this worked fine. Unfortunately the last dictionary update altered the sort order values and moved the goalposts.

So I need something which actually refers to the taxonomy directly.

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

3

Re: Filtering bat records

Hi Rob
could you use the LCReturnHierarchyLevel to return the Order:

dbo.LCReturnHierarchyLevel(YOUR_TLIK,'O')

Ian

4

Re: Filtering bat records

Agree - something based on the Index_Taxon_Hierarchy, Organism  or Index_Taxon_Group  will do the job.  The following uses Index_Taxon_Group. The organism and Index_Taxon_Hierarchy could do the same  but they use  taxon version keys so expanding these would requires more code.

SELECT ITN.TAXON_LIST_ITEM_KEY FROM Index_Taxon_Group ITG INNER JOIN
INDEX_TAXON_NAME ITN ON ITN.RECOMMENDED_TAXON_LIST_ITEM_KEY = ITG.CONTAINED_LIST_ITEM_KEY
WHERE ITG.Taxon_List_Item_Key =
(SELECT DISTINCT INDEX_TAXON_NAME.RECOMMENDED_TAXON_LIST_ITEM_KEY
FROM INDEX_TAXON_NAME  WHERE ACTUAL_NAME = 'CHIROPTERA')

Mike Weideli

5

Re: Filtering bat records

Thanks Mike (and Ian), I'll give those a go.

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre