1 Reply by RobLarge

  • RobLarge
  • RobLarge
  • Veteran
  • Offline
  • From: Wiltshire
  • Registered: 15-07-2008
  • Posts: 812

Re: Report wizard, attribute bug report

I've just spotted a whole host of errors in the REPORT_ATTRIBUTE table, only because I was trying to add some attribute fields and I copied one that did nearly what I wanted and it caused a SQL error.

In the field REPORT_ATTRIBUTE.ATTRIBUTE_SQL all items between REPORT_ATTRIBUTE_KEY = 'NBNSYS0000000100' and 'NBNSYS0000000146' (all of which are attributes in the Taxon/status section) are missing the closing single-quotation mark. e.g.

#REPORT_OUTPUT.[Status:NR]='Yes

Furthermore every one of these attributes has the REPORT_JOIN_KEY set to NBNSYS000000003A. Referring to the REPORT_JOIN table, I think this item also has incorrect syntax in the JOIN_SQL field (perhaps relating to the use of linefeeds rather than spaces to separate the clauses).  Unfortunately I have edited this field and no longer have a record of the original so I can't reproduce the exact error here. Certainly the resultant SQL was missing some spaces either before or after some of the LEFT JOINs.

Anyway, as far as I can tell, this means that none of these 47 attributes will work correctly in the wizard. The fact that no-one else seems to have reported the problem suggests that no-one uses these attributes much. U&nless of course the bug only exisits on our copy of Recorder, which seems unlikely.

Of the five new attributes I added today (all relating to locally designated Taxon/status items) I still have one which doesn't work properly, but so far I can't track down precisely where the problem is. I will of course post more if I learn anything useful.

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

RobLarge's Website

2 Reply by johnvanbreda

  • johnvanbreda
  • Administrator
  • Offline
  • From: UK
  • Registered: 27-04-2006
  • Posts: 1,237

Re: Report wizard, attribute bug report

Hi Rob

Yes you are right, there is indeed an error in the Attribute_SQL for these rows. It can be fixed by the following SQL:

UPDATE Report_Attribute 
SET Attribute_Sql = CAST(Attribute_Sql AS VARCHAR(2000)) + '''' 
WHERE Report_Attribute_Key BETWEEN 'NBNSYS0000000100' AND 'NBNSYS0000000146'
AND RIGHT(CAST(Attribute_Sql AS VARCHAR(2000)),1)<>''''

However, having fixed that I didn't get any errors in the report join. My join_SQL for the relevant record is set to:

FROM #Report_Output  LEFT JOIN Index_Taxon_Name ITN       ON (ITN.Taxon_List_Item_Key = #Report_Output.LIST_ITEM_KEY       AND ITN.System_Supplied_Data=1      AND #REPORT_OUTPUT.Type='T')  LEFT JOIN Index_Taxon_Name ITN2 ON ITN.Recommended_Taxon_List_Item_Key=ITN2.Recommended_Taxon_List_Item_Key  LEFT JOIN Taxon_Designation TD ON TD.Taxon_List_Item_Key=ITN2.Taxon_List_Item_Key

Having said that, what I did find is that some of these listed designations no longer exist in my taxon designation types so they don't work anyway (Protection of Badgers Act is just one example).

Given that these status attributes don't work and are obviously not used, as well as version 6.14 improving status reporting considerably, maybe we should remove them from the Report Wizard attributes in the interests of not cluttering the system with unused functionality?

John van Breda
Biodiverse IT

johnvanbreda's Website

3 Reply by RobLarge

  • RobLarge
  • RobLarge
  • Veteran
  • Offline
  • From: Wiltshire
  • Registered: 15-07-2008
  • Posts: 812

Re: Report wizard, attribute bug report

As I said, I'm not sure there was an error in the join sql John, it's just that when the error in the attribute table made itself apparent I copied the error message into notepad and it looked like there were some missing spaces between clauses, it may well be however, that this was just a result of the way the exception handler produces the bug report (the whole of the offending SQL being displayed on one line, thus ignoring linefeeds) rather than a flaw in the SQL being executed.

I agree that most of these attributes are probably unnecessary and could perhaps be removed, might be a good idea to leave one or two in however, as examples to adventurous types like me who want to create new attributes for particular purposes. As it happens I am not going to use the ones I created anyway. Not now at any rate.

Better still, full documentation of how to do it would be nice. The notes on the Wiki are useful, but far from complete.

Many thanks

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

RobLarge's Website