1 (edited by Janet Simkin 11-02-2019 23:13:10)

Topic: XML report help needed

This report extracts all records for a selected substrate, at least it should. For some reason it is only showing records that also have occurrence data (codes or abundance), which many records don't. I can't see why! It has to be something really obvious - can anyone help?

Post's attachments

BLS_SB2 Substrate Records v2.xml 3.65 kb, 12 downloads since 2019-02-11 

You don't have the permssions to download the attachments of this post.
Janet Simkin
British Lichen Society

2

Re: XML report help needed

You don't need the join to Taxon_Occurrence_Data, it isn't needed for the output and is causing your problem. If you don't want any records without substrates then an INNER JOIN would be better on the Substrate table. 

INNER JOIN Location_Name LN ON LN.Location_Key = S.Location_Key
INNER JOIN Taxon_Occurrence TOCC ON TOCC.Sample_Key = S.Sample_Key

INNER JOIN Substrate SUB on SUB.Substrate_Key = TOCC.Substrate_Key

Mike Weideli

3

Re: XML report help needed

Thanks, I'll try the inner join, but we do need the occurrence data as that is where we hold our small scale habitat codes and DAFOR abundances, and the report has to show those alongside the substrate.
I have the same joins in several reports and the others all correctly show records that do and do not have occurrence data, so I just can't see why this one is working differently.

Janet Simkin
British Lichen Society

4

Re: XML report help needed

Your habitat codes are obtained by a UDF which uses the Taxon Occurrence key so I can't see anything which uses Taxon Occurrence Data. The measurement tables you have in don't seem to have a purpose. However if all your data has small scale habitat codes I would expect it to work. I will look more carefully later.

Mike Weideli

5

Re: XML report help needed

Hi

I think this version will work. You only need tables in the FROM clause that are referred elsewhere in the query and I can't see that either Taxon_Occurrence_Data or the Measurement tables are needed. The  udf's use TOCC.Taxon_Occurrence_Key and not anything directly from Taxon_Occurrence_Data.

Post's attachments

BLS_SB2 Substrate Records v2.xml 3.37 kb, 2 downloads since 2019-02-12 

You don't have the permssions to download the attachments of this post.
Mike Weideli

6

Re: XML report help needed

Of course, now I understand! Thanks for your help, it is working now.
Janet

Janet Simkin
British Lichen Society