1 Reply by Gordon Barker

  • Gordon Barker
  • Veteran
  • Offline
  • Registered: 15-05-2006
  • Posts: 344

Re: Correcting 'data blank' errors

Mostly a question for Mike Weideli, but might be useful for everyone.

Using the JNCC_sy07_Problem_Detail.xml  report I have 182 data blank errors. Stealing from that xml I have run the query

SELECT     DATA AS Expr1, TAXON_OCCURRENCE_DATA.*
FROM         TAXON_OCCURRENCE_DATA
WHERE     (LTRIM(RTRIM(DATA)) = '')

Which gives me the same sequence of Taxon_Occurrence_Keys as in the original report.

The question is can I now delete these items from the Taxon_Occurrence_Data table?

Thanks

Gordon Barker
Biological Survey Data Manager
National Trust

2 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,645

Re: Correcting 'data blank' errors

Gordon

I was reluctant to provide a facility to delete these as users might wish to  investigate and put things right rather than deleting them. These could be corrected via the observation hierarchy if  the data was available,

If you just want to delete them then the following should do the trick .

DELETE FROM  Taxon_Occurrence_data WHERE    (LTRIM(RTRIM(DATA)) = '')

Taxon_Occurrence_data has nothing else linked to it in any way so deleting from it  will  not be  a problem, but do take  a security before  running this. The query will show the number of rows deleted and this should  be what you expect.

   

Mike

Mike Weideli

MikeWeideli's Website

3 Reply by Gordon Barker

  • Gordon Barker
  • Veteran
  • Offline
  • Registered: 15-05-2006
  • Posts: 344

Re: Correcting 'data blank' errors

Understandable. As all the records in question were imported,  there is unlikely to be further data available on these records which probably had a " " entry in the abundance field of the original XL file, so I am happy to delete the blank data.

Gordon Barker
Biological Survey Data Manager
National Trust