1 Reply by charlesroper

  • charlesroper
  • charlesroper
  • Digital Development Manager
  • Offline
  • From: Field Studies Council
  • Registered: 26-03-2006
  • Posts: 906

Re: Sample recorder imported as unchecked

On a large import done recently, the Sample Recorder(s) imported as unticked. Here's a screenshot to demonstrate what I mean:

http://i.minus.com/ib1uiXLdbRsW6a.png

Why would that be? It only seems to have affected this survey (as far as I can see - I've not check extensively) and subsequent imports have not had the same behaviour. This was a very large import, so it could have been related to volume of data, possibly.

Anyway, I need to fix this. Mike, do you have a query that will tick all Sample Recorders in a given survey? If not, I can do it myself.

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

charlesroper's Website

2 Reply by MikeWeideli

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

Re: Sample recorder imported as unchecked

Charles

This isn't as straightforward as it might look. I suspect that  something in the input file may have caused the Sample_Recorder table not to be populated.   If you look at the Survey_Event  Recorders I assume these are correct ?  Has this happended throughout the import ?   The following query will show you where you samples with no Recorders.  It will be possible to create the required entries, but because new keys will be required a Batch Update will be the easiest way. Before going down this route I suggest that double check that there are no other issues with the import.

Select  S.*  from sample S where
not exists (select * from sample_recorder where sample_recorder.sample_Key = s.sample_key)

Mike Weideli

MikeWeideli's Website

3 Reply by charlesroper

  • charlesroper
  • charlesroper
  • Digital Development Manager
  • Offline
  • From: Field Studies Council
  • Registered: 26-03-2006
  • Posts: 906

Re: Sample recorder imported as unchecked

Thanks Mike.

We've got 28,455 records in the database with this problem. When you say that I should check that there are no other problems, what should I be checking for? The records seem otherwise OK. In fact, we only noticed this when reporting on the data and saw that no recorder names were appearing.

Charles

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

charlesroper's Website

4 Reply by MikeWeideli

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

Re: Sample recorder imported as unchecked

If you are happy with what is there then the additional table can be added. Does it look from the query as though this has happened before or are all the records from the latest import ?  This table doesn't have any keys of its own so the entries can be made witha single query, which I will construct for you.

Mike

Mike Weideli

MikeWeideli's Website

5 Reply by MikeWeideli

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

Re: Sample recorder imported as unchecked

I suggest you run the following first. It should give you 28455 rows (or close) as in the last query. If this is OK then run the insert query.  After running the insert query then run the Test Query which should the  return zero rows.

Test Query

SELECT S.SAMPLE_KEY, SER.SE_RECORDER_KEY,S.ENTERED_BY,S.ENTRY_DATE
FROM SURVEY_EVENT_RECORDER SER
INNER JOIN SURVEY_EVENT SE
ON SE.SURVEY_EVENT_KEY = SER.SURVEY_EVENT_KEY
INNER JOIN SAMPLE S
ON S.SURVEY_EVENT_KEY  = SE.SURVEY_EVENT_KEY
WHERE NOT EXISTS(SELECT * FROM SAMPLE_RECORDER  WHERE SAMPLE_RECORDER.SAMPLE_KEY =
S.SAMPLE_KEY)

Insert Query

INSERT INTO SAMPLE_RECORDER
SELECT S.SAMPLE_KEY, SER.SE_RECORDER_KEY,S.ENTERED_BY,S.ENTRY_DATE
FROM SURVEY_EVENT_RECORDER SER
INNER JOIN SURVEY_EVENT SE
ON SE.SURVEY_EVENT_KEY = SER.SURVEY_EVENT_KEY
INNER JOIN SAMPLE S
ON S.SURVEY_EVENT_KEY  = SE.SURVEY_EVENT_KEY
WHERE NOT EXISTS(SELECT * FROM SAMPLE_RECORDER  WHERE SAMPLE_RECORDER.SAMPLE_KEY =
S.SAMPLE_KEY)

Mike Weideli

MikeWeideli's Website

6 Reply by charlesroper

  • charlesroper
  • charlesroper
  • Digital Development Manager
  • Offline
  • From: Field Studies Council
  • Registered: 26-03-2006
  • Posts: 906

Re: Sample recorder imported as unchecked

Thanks Mike, this worked perfectly.

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

charlesroper's Website