Page 1 of 1

People in Limbo

Posted: Mon May 25, 2009 8:52 pm
by laschapas
Hi Nick,

Tools -> People in limbo produces a text file listing the P number & name of people in limbo. This text editor file cannot be left open while editing any of the people listed. To use this information I have to copy the data to an external text editor, close the internal text editor, and then enter the P number in the Edit person window, to work on the person found in the people in limbo test. Would it be possible to produce the output of the people in limbo search as a table that can be clicked to give direct access to the edit person window. I have been playing with the "Links" database using the SQL query feature to try to create the people in limbo test but I don't fully understand the "Links" database to get it to work. The same problem exists with the "find date errors" output.

John Wood

Posted: Wed May 27, 2009 8:30 am
by Nick Hunter
Hi John,

We will definitely look at clickable results lists for these functions.

I don't think that it is possible to create an SQL query which will find the people in limbo.

The Links table holds both "Children" and "Marriages". If the field RecType = "F" then PersFam = family code and ChildMarr = child code. If the field RecType = "P" then PersFam = person code and ChildMarr = marriage code.

SQL query for people in limbo

Posted: Sat Jul 04, 2009 6:10 am
by jhunt02
This query can be used in Kith & Kin to show all people who are in limbo. Furthermore, as you add the people to their families, they immediately disappear from the query result list ("live" update), so you can tell how far along you've gotten. Pretty cool!

-- List all people who are in limbo
select * from People
where Code not in
(select P.Code from People P, Links L where
(P.Code = L.ChildMarr and L.RecType = 'F') or (P.Code = L.PersFam and L.RecType = 'P'));

Posted: Sat Jul 04, 2009 3:51 pm
by laschapas

I have just tried this with my database of over 3000 people and it did produce the same list as the Tools -> People in limbo feature. The result can also be used to directly select a person's edit window, that's just what I wanted, thanks.

The Tools -> People feature produces the list as a text file showing only the Person Description field, in under a second. The query takes 30 seconds on my AMD Athlon XP 3200+ running Windows XP. This query certainly solves the problem I raised on 25 May.

Why not add this query example to the "Favourite SQL queries" topic?

Is it possible to produce a similar query for the date errors problem? There are far more user defined variables to test.

John Wood

Posted: Sat Jul 04, 2009 5:11 pm
by jhunt02

Glad you liked the limbo query. The "date errors" problem is more complex. Theoretically it could be solved with a single SQL query, but the database engine probably can't process queries which are that complex, so the actual testing of date-error conditions is done in the program itself or using multiple queries.

I'm surprised the limbo query takes so long to execute (30 seconds). How many people in limbo did it return when you first tried it out? Execution could possibly be speeded up by adding a couple of indexes on the Links and/or People table, but as I don't work for SpanSoft, I'm afraid can't do that.

Best regards,
John Hunt

Posted: Sat Jul 04, 2009 8:40 pm
by laschapas
Hi John,

I packed the database and retimed the query. It still took 30 seconds to find 22 people from 3023, with 100% CPU Usage for the whole 30 seconds. There is some disc activity but it is not continuous. Running it a second time still took 30 seconds. the people.dat & people.idx files are both about 5Mb

John Wood