1 Reply by charliebarnes

  • charliebarnes
  • Veteran
  • Offline
  • From: Greater Lincolnshire
  • Registered: 07-09-2008
  • Posts: 1,384

Re: Rucksacks and XML reports

I've successfully got my XML report able to use a rucksack (or picked taxa) to filter the list of records returned, but at the moment it only picks up "exact matches" - how do I write it so that whatever name is in the rucksack, it translates this to the preferred name?

Or do I need to make sure that the names in the rucksack are preferred?

LEFT JOIN Index_Taxon_Name ITNr ON ITNr.Taxon_List_Item_key = ITN.Recommended_Taxon_List_item_key

............

<Condition field="ITNr.Taxon_List_Item_key" 
           operator="equal" 
           type="Taxon" 
           name="taxon"
           entrycount="-1"
           userucksack="optional" />
Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

charliebarnes's Website

2 Reply by charliebarnes

  • charliebarnes
  • Veteran
  • Offline
  • From: Greater Lincolnshire
  • Registered: 07-09-2008
  • Posts: 1,384

Re: Rucksacks and XML reports

Ahhh just looking at some of supplied reports and think I might have figured it out... is the only way to do this to create a temp table thus:

CREATE TABLE #Taxa (TLI_Key char(16) COLLATE SQL_Latin1_General_CP1_CI_AS, RecTLI_Key  char(16) COLLATE SQL_Latin1_General_CP1_CI_AS)
INSERT INTO #Taxa (TLI_KEY,RecTLI_Key)
SELECT DISTINCT ITN2.Taxon_List_Item_Key, 
ITN.Recommended_Taxon_List_Item_Key
FROM 
Index_Taxon_Name ITN
INNER JOIN Index_Taxon_Name ITN2 ON ITN.Recommended_Taxon_List_Item_Key =ITN2.Recommended_Taxon_List_Item_Key
WHERE
 
<Condition field= "ITN.Taxon_List_Item_key" entrycount ="-1" operator="equal" type="Taxon"  name="Select Rucksack" userucksack = "yes" />
AND ITN2.System_Supplied_Data = 1
AND ITN.System_Supplied_Data = 1
Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

charliebarnes's Website

3 Reply by MikeWeideli (edited by MikeWeideli 21-06-2011 21:41:07)

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,627

Re: Rucksacks and XML reports

Linking Index_taxon_name in twice through Recommended taxon_list_Item key is the standard approach to this.  I m sure though you don't have to have the temporary  table.  The following should work.  The Rucksack does need to contain species and not higher levels otherwise yu woudl need additional tables to expand out the Rucksack.

SELECT  TOCC.TAXON_OCCURRENCE_KEY  FROM  TAXON_OCCURRENCE TOCC INNER JOIN
TAXON_DETERMINATION TDET ON TDET.TAXON_OCCURRENCE_KEY = TOCC.TAXON_OCCURRENCE_KEY AND TDET.PREFERRED = 1
INNER JOIN INDEX_TAXON_NAME ITN ON ITN.TAXON_LIST_ITEM_KEY = TDET.TAXON_LIST_ITEM_KEY
INNER JOIN INDEX_TAXON_NAME ITN2 ON ITN2.RECOMMENDED_TAXON_LIST_ITEM_KEY =
ITN.RECOMMENDED_TAXON_LIST_ITEM_KEY
WHERE
<Condition field="ITN2.Taxon_List_Item_key"
           operator="equal"
           type="Taxon"
           name="taxon"
           entrycount="-1"
           userucksack="optional" />

Mike Weideli

MikeWeideli's Website