1 Reply by timmay

  • timmay
  • timmay
  • Member
  • Offline
  • From: Cofnod, Bangor, North Wales
  • Registered: 19-01-2007
  • Posts: 24

Re: Pisces in new Dictionary

I've just installed the Recorder 6 dictionary from the Upgrades web page (although I have to admit I was a bit confused by the heading 'Upgrades since V6.9.3', as I have 6.10.4, and didn't know if some of these updates would have been included in the update from 6.9.3 to 6.10.4 - I selected all these options anyway, and now have an updated version of the dictionary that seems ok, except for one point.

I export the dictionary into a flat file to use with our own systems, and use two joins on the Recommended Key and the Taxon Key in the flat file to ensure that the name that is returned is always the most up-to-date name. I check with each update that the dictionary still tallies with the records we have stored. One of our records was entered as Pisces, which has a Version Key of NHMSYS0000841378, and a Recommended Key of NBNSYS0000151610 (in the old dictionary). This corresponds correctly with the preferred entry on the MCNR list. In the updated version of the dictionary this Recommended Key has been changed to NBNSYS0000154965, which doesn't exists as a Taxon Key, even though the record with the previous recommended name still exists.

Although a 'Pisces' record isn't really of that much value(!), I am slightly concerned that this might not be the only item pointing to the wrong place.

Any help would be much appreciated.

Thanks,

Tim

timmay's Website

2 Reply by timmay

  • timmay
  • timmay
  • Member
  • Offline
  • From: Cofnod, Bangor, North Wales
  • Registered: 19-01-2007
  • Posts: 24

Re: Pisces in new Dictionary

Hmm, no answers to be had!

I've just run a search and found two other species which also suffer from the same symptoms - They are 'Leptocera breviceps' and 'Arthrocnodax acarisuga'. For now I've got around it by updating the Recommended Key to point to my own particular favourite.

Tim

timmay's Website

3 Reply by Sarah Shaw

  • Sarah Shaw
  • Veteran
  • Offline
  • Registered: 23-06-2006
  • Posts: 267

Re: Pisces in new Dictionary

Hi Tim

Could you give me a bit more detail on the keys/query you are using?

For example, when you say recommended_key do you mean the Recommended_Taxon_List_Item_key from the Index_Taxon_Name table? Similarly, what is meant by the Taxon_Key?

If you could post abit more info I can do some investigating here.

Many thanks and best wishes,

Sarah Shaw
Biodiversity Information Assistant
JNCC

4 Reply by timmay

  • timmay
  • timmay
  • Member
  • Offline
  • From: Cofnod, Bangor, North Wales
  • Registered: 19-01-2007
  • Posts: 24

Re: Pisces in new Dictionary

Hi Sarah,

Sorry, we have changed the names in our DB to make them a bit less of a mouthful. What I call the Taxon Key is the INDEX_TAXON_NAME.TAXON_LIST_ITEM_KEY, Version Key is TAXON_LIST_ITEM.TAXON_VERSION_KEY, and Recommended Key is INDEX_TAXON_NAME.RECOMMENDED_TAXON_LIST_ITEM_KEY.

Thanks,

Tim

timmay's Website

5 Reply by Sarah Shaw

  • Sarah Shaw
  • Veteran
  • Offline
  • Registered: 23-06-2006
  • Posts: 267

Re: Pisces in new Dictionary

Hi Tim

In my copy of the dictionary (V6.10) the situation is as follows:

- Pisces (Taxon_Version_Key 'NHMSYS0000841378') has a Recommended_Taxon_List_Item_Key of 'NBNSYS0000154965' (Pisces) in the Index_Taxon_Name table (the recommended entry is on the Priority Species List (1998) which isn't ideal but the choices for this Taxon_Version are limited)

- Leptocera breviceps (Taxon_Version_Key 'NBNSYS0000029139') has a Recommended_Taxon_List_Item_Key of 'NBNSYS0100074911' (Rachispoda breviceps) in the Index_Taxon_Name table

- Arthrocnodax acarisuga (Taxon_Version_Key 'NBNSYS0100027509') has a Recommended_Taxon_List_Item_Key of 'NBNSYS0100057875' (Arthrocnodax acarisuga) in the Index_Taxon_Name table

Are all of the above Taxon_List_Item_Keys missing in your copy??

For info this is the query I can ran:-

--PISCES EXAMPLE
--Query finds all TLIK's with a TVK of NHMSYS0000841378 (Pisces)
select TLI.Taxon_list_item_key,
    TV.Taxon_Version_Key,
    T.Item_Name,
    TL.Item_Name
from Taxon_List_Item TLI
inner join taxon_version TV
on TLI.Taxon_Version_key = TV.Taxon_Version_Key
inner join Taxon T
on TV.Taxon_Key = T.Taxon_Key
inner join Taxon_List_Version TLV
on TLI.Taxon_List_Version_Key = TLV.Taxon_List_Version_Key
inner join Taxon_List TL
on TLV.Taxon_List_Key = TL.Taxon_list_key
where TLI.Taxon_Version_Key = 'NHMSYS0000841378'

-- Query finds all entries in Index_Taxon_Name for all TLIK's of Pisces
select *
from Index_Taxon_Name ITN
where ITN.Taxon_list_item_key IN ( 
'NBNSYS0000154965',
'NBNSYS0000002103',
'NBNSYS0000151610',
'EHSSYS0000000042',
'NBNSYS0000170555')

-- Query looks at the Recommended_Taxon_List_Item_key for all TLIK's of Pisces
select TLI.Taxon_list_item_key,
    TV.Taxon_Version_Key,
    T.Item_Name,
    TL.Item_Name
from Taxon_List_Item TLI
inner join taxon_version TV
on TLI.Taxon_Version_key = TV.Taxon_Version_Key
inner join Taxon T
on TV.Taxon_Key = T.Taxon_Key
inner join Taxon_List_Version TLV
on TLI.Taxon_List_Version_Key = TLV.Taxon_List_Version_Key
inner join Taxon_List TL
on TLV.Taxon_List_Key = TL.Taxon_list_key
where TLI.Taxon_list_item_Key = 'NBNSYS0000154965'

Please let me know if you don't have these TLIK's.

Kind regards,

Sarah Shaw
Biodiversity Information Assistant
JNCC

6 Reply by timmay

  • timmay
  • timmay
  • Member
  • Offline
  • From: Cofnod, Bangor, North Wales
  • Registered: 19-01-2007
  • Posts: 24

Re: Pisces in new Dictionary

OK, now I'm confused. Those TLIKs aren't in my export of the dictionary, but they do exist in the TAXON_LIST_ITEM table within Recorder 6. However, the view I have created to export the dictionary to a flat file doesn't pick it up as it doesn't have an entry in the INDEX_TAXON_NAME table, despite having rebuilt the indexes.

My view does an inner join on the TAXON_LIST_ITEM and INDEX_TAXON_NAME tables using the TAXON_LIST_ITEM_KEY present in both, meaning that as the INDEX_TAXON_NAME table doesn't have an entry, I don't get anything out for that item.

Everything else appears to be working well - it's just those three entries.

Thanks,

Tim

timmay's Website

7 Reply by Sarah Shaw

  • Sarah Shaw
  • Veteran
  • Offline
  • Registered: 23-06-2006
  • Posts: 267

Re: Pisces in new Dictionary

Hi Tim

This does sound very strange.

Could you check that you have entries for these taxon_version_keys in your NAMESERVER table?

It would also be worth checking that you have the recommended Taxon_list_item_keys in your database too.

Many thanks,

Sarah Shaw
Biodiversity Information Assistant
JNCC

8 Reply by timmay

  • timmay
  • timmay
  • Member
  • Offline
  • From: Cofnod, Bangor, North Wales
  • Registered: 19-01-2007
  • Posts: 24

Re: Pisces in new Dictionary

Right, here goes.

In the NAMESERVER Table
                                       INPUT_TAXON_VERSION_KEY        RECOMMENDED_TAXON_VERSION_KEY       RECOMMENDED_TAXON_LIST_ITEM_KEY
Pisces                                     NHMSYS0000841378                              NHMSYS0000841378                                   NBNSYS0000154965
Leptocera breviceps                 NBNSYS0000029139                              NBNSYS0100027509                                    NBNSYS0100074911
Arthrocnodax acarisuga            NBNSYS0100027509                              NBNSYS0100027509                                    NBNSYS0100074911

Curiouser and curiouser.

The RECOMMENDED_TAXON_LIST_ITEM_KEYs are all present.

Tim

timmay's Website

9 Reply by Sarah Shaw

  • Sarah Shaw
  • Veteran
  • Offline
  • Registered: 23-06-2006
  • Posts: 267

Re: Pisces in new Dictionary

Hi Tim

This does seem very strange - John from Dorset Software might be able to shed some light.

John - under what circumstances would an entry in Nameserver not be included in the Index_Taxon_Name table when it is rebuilt?

We've had a look at the stored procedure - usp_IndexTaxonName_ApplyNameServer but we can't find the syntax for populating the table initially with Taxon_list_item_keys (only the syntax to update the recommended_taxon_list_item_keys etc. for each entry).

Is this initial population done elsewhere?

Many thanks,

Sarah Shaw
Biodiversity Information Assistant
JNCC