1

Re: Report to pick up empty Observations?

I'm occasionally finding Observations without any Taxon Occurrences (I suspect some may have been lost during import from Recorder 2002). Does anyone have an XML report to pick up empty Observations? Or can anyone give me an indication of the SQL SELECT I need to get started?

Many thanks

Alan

Alan Hale
Aberystwyth

2

Re: Report to pick up empty Observations?

Hi Alan

I don't have a report as such, but the following SQL finds all samples which contain no taxon occurrences:

select s.sample_key 
from [sample] s
left join taxon_occurrence xo on xo.sample_key=s.sample_key
where xo.taxon_occurrence_key is null

I hope that helps,

John van Breda
Biodiverse IT

3

Re: Report to pick up empty Observations?

Hi John

That's great, I just ran it in SQL Server Mgt Tools and it does the trick.

Thanks very much.

Alan

Alan Hale
Aberystwyth

4

Re: Report to pick up empty Observations?

I wondered whether you meant taxon occurrences without determinations – effectively a database corruption or referential integrity problem. If so, the following XML report will find them. Other corruptions I have seen in Recorder 2002 databases are taxon determinations with no Taxon_list_item keys, taxon occurrences with no preferred determinations and taxon occurrences with more than one preferred determination. If problems occur it is worth checking for these. If a taxon occurrence doesn’t have a taxon determination or a determination doesn’t have a valid Taxon_list_item key then you can’t see the occurrence in the observation hierarchy because there is no species name to display.

<?xml version="1.0" ?>
<CustomReport title="Taxon occurrences without determinations" menupath="Database checks" description="Returns a list of taxon occurrences which do not have determinations.">

    <SQL>
    SELECT
        TAXON_OCCURRENCE.TAXON_OCCURRENCE_KEY
    FROM TAXON_OCCURRENCE LEFT JOIN TAXON_DETERMINATION ON TAXON_OCCURRENCE.TAXON_OCCURRENCE_KEY = TAXON_DETERMINATION.TAXON_OCCURRENCE_KEY
    <Where keytype="Default">   
    WHERE
        (TAXON_DETERMINATION.TAXON_DETERMINATION_KEY) Is Null
    </Where>
   
    </SQL>

<Columns>
</Columns>

</CustomReport>

Sally Rankin, JNCC Recorder Approved Expert
E-mail: s.rankin@btinternet.com
Telephone: 01491 578633
Mobile: 07941 207687

5

Re: Report to pick up empty Observations?

Hi Sally

Thanks very much for this - I will check it out.

It looks like I am going go have to spend some time cleaning up my Recorder 6 data. The SQL John gave me showed me for example that NONE of the taxon occurrences in a lichen dataset (Survey) were brought over from R2K2 (you'd think I might have noticed that anyway!). There seem to be less obvious gaps in other datasets.

All the best

Alan

Alan Hale
Aberystwyth