|
The technical forum of the NBN Trust for use by the Network. It includes discussions and announcements on the NBN Atlas, Recorder 6 and Indicia |
You are not logged in. Please login or register.
Forum → Reporting & XML Reports → 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?
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
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.
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.
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.
Of course, now I understand! Thanks for your help, it is working now.
Janet
Forum → Reporting & XML Reports → XML report help needed
Powered by PunBB 1.4.6, supported by Informer Technologies, Inc.