Re: Report returns multiple rows for a single taxa record
Hi all,
I've written a report which browses all records (into a MapMate Import Spec format) from either a Survey or a Taxon Dictionary, however, duplicates all records i.e. for a survey of 2658 Large Yellow Underwing records it returns over 19,000 lines of output with all records clearly duplicated x number of times.
I'd be grateful if someone could take a look at the following code to see where the problem lies and point me in the right direction.
<?xml version="1.0" encoding="iso-8859-1" ?>
<!-- Browse all Records for Import to MapMate v1.7
XML Report by Les Hill of the National Moth Recording Scheme
http://www.mothscount.org/
Version 1.7 - 23/11/2009
-->
<CustomReport menupath="National Moth Recording Scheme" title="Browse all Records for Import to MapMate v1.7" description="Browse all records. Output formatted to the MapMate Import Specification">
<SQL>SELECT
ITN.PREFERRED_NAME + ISNULL(' ' + ITN.Authority, '') AS Preferred_Name,
SE.Location_Name AS Location_Name,
LN.Location_Key,
SE.Spatial_Ref,
SE.Spatial_Ref_System,
SE.Lat,
SE.Long,
CASE
WHEN LN.Item_Name = 'West Cornwall' THEN '1'
WHEN LN.Item_Name = 'East Cornwall' THEN '2'
WHEN LN.Item_Name = 'South Devon' THEN '3'
WHEN LN.Item_Name = 'North Devon' THEN '4'
WHEN LN.Item_Name = 'South Somerset' THEN '5'
WHEN LN.Item_Name = 'North Somerset' THEN '6'
WHEN LN.Item_Name = 'North Wiltshire' THEN '7'
WHEN LN.Item_Name = 'South Wiltshire' THEN '8'
WHEN LN.Item_Name = 'Dorset' THEN '9'
WHEN LN.Item_Name = 'Isle of Wight' THEN '10'
WHEN LN.Item_Name = 'South Hampshire' THEN '11'
WHEN LN.Item_Name = 'North Hampshire' THEN '12'
WHEN LN.Item_Name = 'West Sussex' THEN '13'
WHEN LN.Item_Name = 'East Sussex' THEN '14'
WHEN LN.Item_Name = 'East Kent' THEN '15'
WHEN LN.Item_Name = 'West Kent' THEN '16'
WHEN LN.Item_Name = 'Surrey' THEN '17'
WHEN LN.Item_Name = 'South Essex' THEN '18'
WHEN LN.Item_Name = 'North Essex' THEN '19'
WHEN LN.Item_Name = 'Hertfordshire' THEN '20'
WHEN LN.Item_Name = 'Middlesex' THEN '21'
WHEN LN.Item_Name = 'Berkshire' THEN '22'
WHEN LN.Item_Name = 'Oxfordshire' THEN '23'
WHEN LN.Item_Name = 'Buckinghamshire' THEN '24'
WHEN LN.Item_Name = 'East Suffolk' THEN '25'
WHEN LN.Item_Name = 'West Suffolk' THEN '26'
WHEN LN.Item_Name = 'East Norfolk' THEN '27'
WHEN LN.Item_Name = 'West Norfolk' THEN '28'
WHEN LN.Item_Name = 'Cambridgeshire' THEN '29'
WHEN LN.Item_Name = 'Bedfordshire' THEN '30'
WHEN LN.Item_Name = 'Huntingdonshire' THEN '31'
WHEN LN.Item_Name = 'Northamptonshire' THEN '32'
WHEN LN.Item_Name = 'East Gloucestershire' THEN '33'
WHEN LN.Item_Name = 'West Gloucestershire' THEN '34'
WHEN LN.Item_Name = 'Monmouthshire' THEN '35'
WHEN LN.Item_Name = 'Herefordshire' THEN '36'
WHEN LN.Item_Name = 'Worcestershire' THEN '37'
WHEN LN.Item_Name = 'Warwickshire' THEN '38'
WHEN LN.Item_Name = 'Staffordshire' THEN '39'
WHEN LN.Item_Name = 'Shropshire' THEN '40'
WHEN LN.Item_Name = 'Glamorganshire' THEN '41'
WHEN LN.Item_Name = 'Breconshire' THEN '42'
WHEN LN.Item_Name = 'Radnorshire' THEN '43'
WHEN LN.Item_Name = 'Carmarthenshire (Caerfyrddyn)' THEN '44'
WHEN LN.Item_Name = 'Pembrokeshire' THEN '45'
WHEN LN.Item_Name = 'Cardiganshire (Ceredigion)' THEN '46'
WHEN LN.Item_Name = 'Montgomeryshire' THEN '47'
WHEN LN.Item_Name = 'Meirionydd (Merionithshire)' THEN '48'
WHEN LN.Item_Name = 'Caernarvonshire (Caernarfon)' THEN '49'
WHEN LN.Item_Name = 'Denbighshire' THEN '50'
WHEN LN.Item_Name = 'Flintshire' THEN '51'
WHEN LN.Item_Name = 'Anglesey' THEN '52'
WHEN LN.Item_Name = 'South Lincolnshire' THEN '53'
WHEN LN.Item_Name = 'North Lincolnshire' THEN '54'
WHEN LN.Item_Name = 'Leicestershire (with Rutland)' THEN '55'
WHEN LN.Item_Name = 'Nottinghamshire' THEN '56'
WHEN LN.Item_Name = 'Derbyshire' THEN '57'
WHEN LN.Item_Name = 'Cheshire' THEN '58'
WHEN LN.Item_Name = 'South Lancashire' THEN '59'
WHEN LN.Item_Name = 'West Lancashire' THEN '60'
WHEN LN.Item_Name = 'South-east Yorkshire' THEN '61'
WHEN LN.Item_Name = 'North-east Yorkshire' THEN '62'
WHEN LN.Item_Name = 'South-west Yorkshire' THEN '63'
WHEN LN.Item_Name = 'Mid-west Yorkshire' THEN '64'
WHEN LN.Item_Name = 'North-west Yorkshire' THEN '65'
WHEN LN.Item_Name = 'Durham' THEN '66'
WHEN LN.Item_Name = 'South Northumberland' THEN '67'
WHEN LN.Item_Name = 'North Northumberland (Cheviotland)' THEN '68'
WHEN LN.Item_Name LIKE 'Westmorland%North Lancashire' THEN '69'
WHEN LN.Item_Name = 'Cumberland' THEN '70'
WHEN LN.Item_Name = 'Isle of Man' THEN '71'
WHEN LN.Item_Name = 'Dumfriesshire' THEN '72'
WHEN LN.Item_Name = 'Kirkcudbrightshire' THEN '73'
WHEN LN.Item_Name = 'Wigtownshire' THEN '74'
WHEN LN.Item_Name = 'Ayrshire' THEN '75'
WHEN LN.Item_Name = 'Renfrewshire' THEN '76'
WHEN LN.Item_Name = 'Lanarkshire' THEN '77'
WHEN LN.Item_Name = 'Peebleshire' THEN '78'
WHEN LN.Item_Name = 'Selkirkshire' THEN '79'
WHEN LN.Item_Name = 'Roxburghshire' THEN '80'
WHEN LN.Item_Name = 'Berwickshire' THEN '81'
WHEN LN.Item_Name = 'East Lothian (Haddington)' THEN '82'
WHEN LN.Item_Name = 'Midlothian (Edinburgh)' THEN '83'
WHEN LN.Item_Name = 'West Lothian (Linlithgowshire)' THEN '84'
WHEN LN.Item_Name = 'Fifeshire' THEN '85'
WHEN LN.Item_Name = 'Stirlingshire' THEN '86'
WHEN LN.Item_Name = 'West Perthshire' THEN '87'
WHEN LN.Item_Name = 'Mid Perthshire' THEN '88'
WHEN LN.Item_Name = 'East Perthshire' THEN '89'
WHEN LN.Item_Name = 'Angus (Forfarshire)' THEN '90'
WHEN LN.Item_Name = 'Kincardineshire' THEN '91'
WHEN LN.Item_Name = 'South Aberdeenshire' THEN '92'
WHEN LN.Item_Name = 'North Aberdeenshire' THEN '93'
WHEN LN.Item_Name = 'Banffshire' THEN '94'
WHEN LN.Item_Name = 'Moray (Elgin)' THEN '95'
WHEN LN.Item_Name LIKE 'East Inverness%Nairn (Easterness)' THEN '96'
WHEN LN.Item_Name = 'West Inverness-shire' THEN '97'
WHEN LN.Item_Name = 'Main Argyll' THEN '98'
WHEN LN.Item_Name = 'Dunbartonshire' THEN '99'
WHEN LN.Item_Name = 'Clyde Islands' THEN '100'
WHEN LN.Item_Name = 'Kintyre' THEN '101'
WHEN LN.Item_Name = 'South Ebudes' THEN '102'
WHEN LN.Item_Name = 'Mid Ebudes' THEN '103'
WHEN LN.Item_Name = 'North Ebudes' THEN '104'
WHEN LN.Item_Name = 'West Ross.' THEN '105'
WHEN LN.Item_Name = 'East Ross.' THEN '106'
WHEN LN.Item_Name = 'East Sutherland' THEN '107'
WHEN LN.Item_Name = 'West Sutherland' THEN '108'
WHEN LN.Item_Name = 'Caithness' THEN '109'
WHEN LN.Item_Name = 'Outer Hebrides' THEN '110'
WHEN LN.Item_Name = 'Orkney islands' THEN '111'
WHEN LN.Item_Name = 'Shetland (Zetland)' THEN '112'
WHEN LN.Item_Name = 'Channel Islands' THEN '113'
WHEN LN.Item_Name = 'Fermanagh' THEN 'H33'
WHEN LN.Item_Name = 'East Donegal' THEN 'H34'
WHEN LN.Item_Name = 'West Donegal' THEN 'H35'
WHEN LN.Item_Name = 'Tyrone' THEN 'H36'
WHEN LN.Item_Name = 'Armagh' THEN 'H37'
WHEN LN.Item_Name = 'Down' THEN 'H38'
WHEN LN.Item_Name = 'Antrim' THEN 'H39'
WHEN LN.Item_Name = 'Londonderry' THEN 'H40'
ELSE
LN.Item_Name END AS VC,
dbo.FormatEventRecorders(S.Sample_Key) AS Recorder,
dbo.ufn_GetFormattedName(TD.DETERMINER) AS Determiner,
CASE
WHEN SE.VAGUE_DATE_START = SE.VAGUE_DATE_END
THEN
dbo.LCReturnVagueDateShort(SE.VAGUE_DATE_START, SE.VAGUE_DATE_END, 'D')
ELSE
dbo.LCReturnVagueDateShort(SE.VAGUE_DATE_START, SE.VAGUE_DATE_END, 'DD')
END AS Date,
CASE
WHEN XOD.Data = 'Present' THEN '0'
ELSE
XOD.Data
END As Quantity,
ST.Short_Name AS Method,
CASE
WHEN MQ.Short_Name LIKE 'Adult Female' THEN 'Female'
WHEN MQ.Short_Name LIKE 'Adult Male' THEN 'Male'
ELSE
'Not recorded'
END AS Sex,
CASE
WHEN MQ.Short_Name LIKE '%Adult%' THEN 'Adult'
WHEN MQ.Short_Name = 'Larvae' THEN 'Larval'
ELSE
MQ.Short_Name
END AS Stage,
'Not recorded' AS Status,
CASE WHEN TXO.Confidential = '0' THEN 'No' ELSE 'Yes' END AS Confidential,
TXO.Comment
FROM Taxon_Occurrence TXO
INNER JOIN Taxon_Determination TD ON TXO.Taxon_Occurrence_key = TD.Taxon_Occurrence_key AND TD.Preferred = 1
INNER JOIN Index_Taxon_name ITN ON ITN.Taxon_List_Item_Key = TD.Taxon_List_Item_Key
INNER JOIN Index_Taxon_Name ITN2 ON ITN2.Recommended_Taxon_List_Item_Key = ITN.Recommended_Taxon_List_Item_Key
INNER JOIN Taxon_Occurrence_Data AS XOD ON TXO.Taxon_Occurrence_Key = XOD.Taxon_Occurrence_Key
INNER JOIN Measurement_Qualifier MQ ON MQ.Measurement_Qualifier_Key = XOD.Measurement_Qualifier_Key
INNER JOIN [Sample] S ON S.Sample_Key = TXO.Sample_Key
INNER JOIN Sample_Type ST ON ST.Sample_Type_Key = S.Sample_Type_Key
INNER JOIN SURVEY_EVENT AS SE ON S.SURVEY_EVENT_KEY = SE.SURVEY_EVENT_KEY
INNER JOIN SURVEY_EVENT_RECORDER AS SER ON SE.SURVEY_EVENT_KEY = SER.SURVEY_EVENT_KEY
INNER JOIN INDIVIDUAL AS I ON SER.NAME_KEY = I.NAME_KEY
INNER JOIN Location L ON L.Location_Key = S.Location_Key
INNER JOIN Location_Name LN ON LN.Location_Key = L.Location_Key AND LN.Preferred = 1
WHERE
<Where keytype="Default">
<Condition field="ITN.TAXON_LIST_ITEM_KEY" operator="equal" type="Taxon" name="Select..." />
ORDER BY SE.VAGUE_DATE_START, ITN.SORT_ORDER ASC
</Where>
<Where keytype="Taxon">
ITN.Taxon_List_Item_Key = '%s'
ORDER BY SE.VAGUE_DATE_START, ITN.SORT_ORDER ASC
</Where>
<Where keytype="Survey">
SE.SURVEY_KEY = '%s'
ORDER BY SE.VAGUE_DATE_START, ITN.SORT_ORDER ASC
</Where>
</SQL>
<Columns>
<Column name="Preferred_Name" caption="Taxon" width="100" position="1" />
<Column name="Location_Name" caption="Site" width="100" keycolumn="Location_Key" position="2" />
<Column name="Spatial_Ref" caption="Gridref" width="100" position="3" />
<Column name="VC" caption="VC" width="40" position="4" />
<Column name="Recorder" caption="Recorder" width="100" position="5" />
<Column name="Determiner" caption="Determiner" width="100" position="6" />
<Column name="Date" caption="Date" width="100" position="7" />
<Column name="Quantity" caption="Quantity" width="100" position="8" />
<Column name="Method" caption="Method" width="100" position="9" />
<Column name="Sex" caption="Sex" width="100" position="10" />
<Column name="Stage" caption="Stage" width="100" position="11" />
<Column name="Status" caption="Status" width="100" position="12" />
<Column name="Confidential" caption="Confidential" width="75" position="13" />
<Column name="Comment" caption="Comment" width="250" position="14" />
<Column name="Location_Key" visible="false" tablename="Location" />
</Columns>
</CustomReport>Les Evans-Hill
Senior Data Officer, National Moth Recording Scheme
Senior Data Officer, National Moth Recording Scheme
