SQLite Tools for RootsMagic

Exploit your RootsMagic family tree database with SQLite Tools

AllCitations.sql Error

Posted on by chief superintendent

Update: 20 Jan 2021
New versions of sql code added to original post All Citations – Query #citations

I am running the following query in SQLite Expert Personal 5.2(x86):

-- AllCitations.sql
-- 2010-01-28 ve3meo
-- Lists citations for each person
-- 2010-01-29 rev by ve3meo to use LEFT OUTER JOINS to include the most orphaned citations
-- Citations for Alternate Names, added column for NameTable.IsPrimary AS Uniq to all queries
-- and negated it for Alt Name and Couple.Wife queries; filter on Uniq for principal name to
-- reduce multiple listing of same citation OR Uniq ISNULL for citations unlinked to persons.
-- Requires a temp table because of speed degradation when incorporated in main selects;
-- filtering can be done on screen in SQLiteDeveloper.
-- 2010-01-30 rev by ve3meo. Dropped UNIQUE from INDEX because other SQLite managers objected.
-- Put QUOTE() around BLOB type fields from CitationTable to display text where some SQLite
-- managers merely say BLOB.
-- To Do - maybe add eventtable.SortDate as a sorting criterion
-- 2011-11-04 ve3meo corrections for spouse, and family fact citations and multiples due Alt Name
-- 2020-12-30 ve3meo incorporated Pat Jones's correction for error reported in comments
-- BEGIN
--
-- all Personal citations for Individual
DROP TABLE IF EXISTS tmpCitations;
CREATE TEMP TABLE tmpCitations AS
SELECT c.CITATIONID AS CitID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary AS Uniq, n.surname COLLATE NOCASE AS Surname, n.suffix COLLATE NOCASE AS Sfx, n.prefix COLLATE NOCASE AS Pfx, n.given COLLATE NOCASE AS Givens, n.birthyear AS Born,
n.deathyear AS Died, 'Personal' AS Citer, s.NAME COLLATE NOCASE AS Source, s.refnumber AS SrcREFN, s.actualtext AS SrcTxt, s.comments AS SrcComment, c.refnumber AS CitREFN,
QUOTE(c.actualtext) AS CitTxt, QUOTE(c.comments) AS CitComment
FROM citationtable c
LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
LEFT OUTER JOIN persontable p ON c.ownerid=p.personid
LEFT OUTER JOIN nametable n ON p.personid=n.ownerid
WHERE c.ownertype=0 AND +n.IsPrimary=1
;
INSERT INTO tmpCitations
-- all Fact citations for Individual
SELECT c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary, n.surname COLLATE NOCASE , n.suffix COLLATE NOCASE , n.prefix COLLATE NOCASE , n.given COLLATE NOCASE , n.birthyear,
n.deathyear, f.NAME AS Citer, s.NAME COLLATE NOCASE , s.refnumber, s.actualtext, s.comments, c.refnumber,
c.actualtext, c.comments
FROM citationtable c
LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
LEFT OUTER JOIN eventtable e ON c.ownerid=e.eventid
LEFT OUTER JOIN persontable p ON e.ownerid=p.personid
LEFT OUTER JOIN nametable n ON p.personid=n.ownerid
LEFT OUTER JOIN facttypetable f ON e.eventtype=f.facttypeid
WHERE c.ownertype=2 AND e.ownertype=0 AND f.ownertype=0 AND +n.IsPrimary=1
;

INSERT INTO tmpCitations
-- all Spouse citations for Father|Husband|Partner 1
SELECT c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary, n.surname, n.suffix, n.prefix, n.given, n.birthyear,
n.deathyear, 'Spouse' as 'Citer', s.NAME, s.refnumber, s.actualtext, s.comments, c.refnumber,
c.actualtext, c.comments
FROM citationtable c
LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
LEFT OUTER JOIN familytable fm ON c.ownerid=fm.FamilyID
LEFT OUTER JOIN persontable p ON fm.fatherid=p.personid
LEFT OUTER JOIN nametable n ON p.personid=n.ownerid
-- LEFT OUTER JOIN eventtable e ON e.ownerid=fm.familyid
-- LEFT OUTER JOIN facttypetable f ON e.eventtype=f.facttypeid
WHERE c.ownertype=1 -- AND e.ownertype=1 AND f.ownertype=1
AND +n.IsPrimary=1
;

INSERT INTO tmpCitations
-- all Couple Event citations for Father|Husband|Partner 1
SELECT c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary, n.surname, n.suffix, n.prefix, n.given, n.birthyear,
n.deathyear, f.NAME, s.NAME, s.refnumber, s.actualtext, s.comments, c.refnumber,
c.actualtext, c.comments
FROM citationtable c
LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
LEFT OUTER JOIN eventtable e ON e.eventid=c.ownerid
LEFT OUTER JOIN familytable fm ON e.ownerid=fm.familyid
LEFT OUTER JOIN persontable p ON fm.fatherid=p.personid
LEFT OUTER JOIN nametable n ON p.personid=n.ownerid
LEFT OUTER JOIN facttypetable f ON e.eventtype=f.facttypeid
WHERE c.ownertype=2 AND e.ownertype=1 AND f.ownertype=1 AND n.IsPrimary=1
;

INSERT INTO tmpCitations
-- Citations for Alternate Names
SELECT c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, NOT n.IsPrimary, n.surname, n.suffix, n.prefix, n.given, n.birthyear,
n.deathyear, 'Alternate Name' AS Citer, s.NAME AS Source, s.refnumber, s.actualtext, s.comments, c.refnumber,
c.actualtext, c.comments
FROM citationtable c
LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
LEFT OUTER JOIN nametable n ON n.nameid=c.ownerid
WHERE c.ownertype=7 AND +n.IsPrimary=0
;
CREATE INDEX tmpCitations_idx ON tmpCitations(CitID);
-- Now filter the results to get rid of duplicate citation IDs due Alt Names
SELECT * FROM tmpcitations
WHERE uniq=1 OR uniq ISNULL
ORDER BY RIN, Citer , SOURCE
;
-- CitID
-- RIN, Citer;
-- END

Rev 2020-12-30 as stated in body plus reposted in block code format to overcome WordPress rendering it into something different

Discussions & comments from Wikispaces site


ve3meo

SQLite behaviour changed in Jan 2017

ve3meo
28 December 2017 21:20:11

The query still works in SQLiteSpy which was compiled with SQLite 3.13.0 but throws this error in my almost current version of SQLite Expert Personal compiled with SQLite 3.19.3. A Google search on the error message returns this ticket https://www.sqlite.org/src/info/25e335f802dd which reports the issue of SQLite3 behaving differently from PostGres which throws the error. It was pretty quickly changed. However, it may take some noodling to understand how to go about changing the above script so that it won’t throw the error in these later SQLite managers while supporting the intended inclusiveness by using the LEFT JOINs.

10 Replies to “AllCitations.sql Error”

  1. The joins in the red section are incorrect and should be:
    FROM citationtable c
    LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
    LEFT OUTER JOIN eventtable e ON e.eventid=c.ownerid
    LEFT OUTER JOIN familytable fm ON e.ownerid=fm.familyid
    LEFT OUTER JOIN persontable p ON fm.fatherid=p.personid
    LEFT OUTER JOIN nametable n ON p.personid=n.ownerid
    LEFT OUTER JOIN facttypetable f ON e.eventtype=f.facttypeid
    WHERE c.ownertype=2 AND e.ownertype=1 AND f.ownertype=1 AND n.IsPrimary=1

  2. to get the above to run I have changed the hyphen to ‘ but still no joy on the last 3 lines can someone please fix for me. I have also corrected the red text with Pat Jones Reply.
    AllCitations.sql
    ‘2010-01-28 ve3meo
    Lists citations for each person
    2010-01-29 rev by ve3meo to use LEFT OUTER JOINS to include the most orphaned citations
    Citations for Alternate Names, added column for NameTable.IsPrimary AS Uniq to all queries
    and negated it for Alt Name and Couple.Wife queries; filter on Uniq for principal name to
    reduce multiple listing of same citation OR Uniq ISNULL for citations unlinked to persons.
    Requires a temp table because of speed degradation when incorporated in main selects;
    filtering can be done on screen in SQLiteDeveloper.
    2010-01-30 rev by ve3meo. Dropped UNIQUE from INDEX because other SQLite managers objected.
    Put QUOTE() around BLOB type fields from CitationTable to display text where some SQLite
    managers merely say BLOB.
    To Do – maybe add eventtable.SortDate as a sorting criterion
    2011-11-04 ve3meo corrections for spouse, and family fact citations and multiples due Alt Name’

    ‘BEGIN
    all Personal citations for Individual’
    DROP TABLE IF EXISTS tmpCitations;
    CREATE TEMP TABLE tmpCitations AS
    SELECT c.CITATIONID AS CitID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary AS Uniq, n.surname COLLATE NOCASE AS Surname, n.suffix COLLATE NOCASE AS Sfx, n.prefix COLLATE NOCASE AS Pfx, n.given COLLATE NOCASE AS Givens, n.birthyear AS Born,
    n.deathyear AS Died, ‘Personal’ AS Citer, s.NAME COLLATE NOCASE AS Source, s.refnumber AS SrcREFN, s.actualtext AS SrcTxt, s.comments AS SrcComment, c.refnumber AS CitREFN,
    QUOTE(c.actualtext) AS CitTxt, QUOTE(c.comments) AS CitComment
    FROM citationtable c
    LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
    LEFT OUTER JOIN persontable p ON c.ownerid=p.personid
    LEFT OUTER JOIN nametable n ON p.personid=n.ownerid
    WHERE c.ownertype=0 AND +n.IsPrimary=1
    ;
    INSERT INTO tmpCitations
    ‘all Fact citations for Individual’
    SELECT c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary, n.surname COLLATE NOCASE , n.suffix COLLATE NOCASE , n.prefix COLLATE NOCASE , n.given COLLATE NOCASE , n.birthyear,
    n.deathyear, f.NAME AS Citer, s.NAME COLLATE NOCASE , s.refnumber, s.actualtext, s.comments, c.refnumber,
    c.actualtext, c.comments
    FROM citationtable c
    LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
    LEFT OUTER JOIN eventtable e ON c.ownerid=e.eventid
    LEFT OUTER JOIN persontable p ON e.ownerid=p.personid
    LEFT OUTER JOIN nametable n ON p.personid=n.ownerid
    LEFT OUTER JOIN facttypetable f ON e.eventtype=f.facttypeid
    WHERE c.ownertype=2 AND e.ownertype=0 AND f.ownertype=0 AND +n.IsPrimary=1
    ;

    INSERT INTO tmpCitations
    ‘all Spouse citations for Father|Husband|Partner 1’
    SELECT c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary, n.surname, n.suffix, n.prefix, n.given, n.birthyear,
    n.deathyear, ‘Spouse’ as ‘Citer’, s.NAME, s.refnumber, s.actualtext, s.comments, c.refnumber,
    c.actualtext, c.comments
    FROM citationtable c
    LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
    LEFT OUTER JOIN familytable fm ON c.ownerid=fm.FamilyID
    LEFT OUTER JOIN persontable p ON fm.fatherid=p.personid
    LEFT OUTER JOIN nametable n ON p.personid=n.ownerid
    — LEFT OUTER JOIN eventtable e ON e.ownerid=fm.familyid
    — LEFT OUTER JOIN facttypetable f ON e.eventtype=f.facttypeid
    WHERE c.ownertype=1 — AND e.ownertype=1 AND f.ownertype=1
    AND +n.IsPrimary=1
    ;

    INSERT INTO tmpCitations
    ‘all Couple Event citations for Father|Husband|Partner 1’
    SELECT c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary, n.surname, n.suffix, n.prefix, n.given, n.birthyear,
    n.deathyear, f.NAME, s.NAME, s.refnumber, s.actualtext, s.comments, c.refnumber,
    c.actualtext, c.comments
    FROM citationtable c
    LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
    LEFT OUTER JOIN eventtable e ON e.eventid=c.ownerid
    LEFT OUTER JOIN familytable fm ON e.ownerid=fm.familyid
    LEFT OUTER JOIN persontable p ON fm.fatherid=p.personid
    LEFT OUTER JOIN nametable n ON p.personid=n.ownerid
    LEFT OUTER JOIN facttypetable f ON e.eventtype=f.facttypeid
    WHERE c.ownertype=2 AND e.ownertype=1 AND f.ownertype=1 AND n.IsPrimary=1
    ;

    INSERT INTO tmpCitations
    ‘Citations for Alternate Names’
    SELECT c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, NOT n.IsPrimary, n.surname, n.suffix, n.prefix, n.given, n.birthyear,
    n.deathyear, ‘Alternate Name’ AS Citer, s.NAME AS Source, s.refnumber, s.actualtext, s.comments, c.refnumber,
    c.actualtext, c.comments
    FROM citationtable c
    LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
    LEFT OUTER JOIN nametable n ON n.nameid=c.ownerid
    WHERE c.ownertype=7 AND +n.IsPrimary=0
    ;
    CREATE INDEX tmpCitations_idx ON tmpCitations(CitID);
    ‘Now filter the results to get rid of duplicate citation IDs due Alt Names’
    SELECT * FROM tmpcitations
    WHERE uniq=1 OR uniq ISNULL
    ORDER BY RIN, Citer , SOURCE
    ;
    CitID
    RIN, Citer;
    END

  3. There is some unfortunate style translation going on in WP. A comment line in SQLite is started with double hyphen: “–” (sans quotes). That has been translated into a special character: “—” which would throw an error. I should think that even surrounding the text with single quotes, e.g., ‘comment’ would also be an error. The three lines at the end were all commented out in the original and now are exposed as 1 or 2 statements that are meaningless to SQLite. Delete them or preface them with the comment control, the double hyphen.

  4. Thanks Tom. A similar issue appears to have occurred with ‘Personal’.

    On paste I get a this `Personal with a corresponding reverse `.

    I have tried no single quotes (if they are single quotes) with no success. no such column error message, the same error message as above.
    An ‘ either side returns a green comment.
    Any ideas.

    Regards Wayne b

  5. I think the problem is with the way WordPress is rendering SQLite code combinations such as two hyphens — to denote a comment line and the slash-asterix combos for a multi-line comment /* comments here */.

    I’ll paste the code here with Pat Jones’s correction and see how it turns out. I’m not aware of how one might denote this block of text as code in the Comment editor…

    -- AllCitations.sql
    -- 2010-01-28 ve3meo
    -- Lists citations for each person
    -- 2010-01-29 rev by ve3meo to use LEFT OUTER JOINS to include the most orphaned citations
    -- Citations for Alternate Names, added column for NameTable.IsPrimary AS Uniq to all queries
    -- and negated it for Alt Name and Couple.Wife queries; filter on Uniq for principal name to
    -- reduce multiple listing of same citation OR Uniq ISNULL for citations unlinked to persons.
    -- Requires a temp table because of speed degradation when incorporated in main selects;
    -- filtering can be done on screen in SQLiteDeveloper.
    -- 2010-01-30 rev by ve3meo. Dropped UNIQUE from INDEX because other SQLite managers objected.
    -- Put QUOTE() around BLOB type fields from CitationTable to display text where some SQLite
    -- managers merely say BLOB.
    -- To Do - maybe add eventtable.SortDate as a sorting criterion
    -- 2011-11-04 ve3meo corrections for spouse, and family fact citations and multiples due Alt Name
    --
    -- BEGIN
    -- all Personal citations for Individual
    DROP TABLE IF EXISTS tmpCitations;
    CREATE TEMP TABLE tmpCitations AS
    SELECT c.CITATIONID AS CitID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary AS Uniq, n.surname COLLATE NOCASE AS Surname, n.suffix COLLATE NOCASE AS Sfx, n.prefix COLLATE NOCASE AS Pfx, n.given COLLATE NOCASE AS Givens, n.birthyear AS Born,
    n.deathyear AS Died, 'Personal' AS Citer, s.NAME COLLATE NOCASE AS Source, s.refnumber AS SrcREFN, s.actualtext AS SrcTxt, s.comments AS SrcComment, c.refnumber AS CitREFN,
    QUOTE(c.actualtext) AS CitTxt, QUOTE(c.comments) AS CitComment
    FROM citationtable c
    LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
    LEFT OUTER JOIN persontable p ON c.ownerid=p.personid
    LEFT OUTER JOIN nametable n ON p.personid=n.ownerid
    WHERE c.ownertype=0 AND +n.IsPrimary=1
    ;
    INSERT INTO tmpCitations
    -- all Fact citations for Individual
    SELECT c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary, n.surname COLLATE NOCASE , n.suffix COLLATE NOCASE , n.prefix COLLATE NOCASE , n.given COLLATE NOCASE , n.birthyear,
    n.deathyear, f.NAME AS Citer, s.NAME COLLATE NOCASE , s.refnumber, s.actualtext, s.comments, c.refnumber,
    c.actualtext, c.comments
    FROM citationtable c
    LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
    LEFT OUTER JOIN eventtable e ON c.ownerid=e.eventid
    LEFT OUTER JOIN persontable p ON e.ownerid=p.personid
    LEFT OUTER JOIN nametable n ON p.personid=n.ownerid
    LEFT OUTER JOIN facttypetable f ON e.eventtype=f.facttypeid
    WHERE c.ownertype=2 AND e.ownertype=0 AND f.ownertype=0 AND +n.IsPrimary=1
    ;

    INSERT INTO tmpCitations
    -- all Spouse citations for Father|Husband|Partner 1
    SELECT c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary, n.surname, n.suffix, n.prefix, n.given, n.birthyear,
    n.deathyear, 'Spouse' as 'Citer', s.NAME, s.refnumber, s.actualtext, s.comments, c.refnumber,
    c.actualtext, c.comments
    FROM citationtable c
    LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
    LEFT OUTER JOIN familytable fm ON c.ownerid=fm.FamilyID
    LEFT OUTER JOIN persontable p ON fm.fatherid=p.personid
    LEFT OUTER JOIN nametable n ON p.personid=n.ownerid
    -- LEFT OUTER JOIN eventtable e ON e.ownerid=fm.familyid
    -- LEFT OUTER JOIN facttypetable f ON e.eventtype=f.facttypeid
    WHERE c.ownertype=1 -- AND e.ownertype=1 AND f.ownertype=1
    AND +n.IsPrimary=1
    ;

    INSERT INTO tmpCitations
    -- all Couple Event citations for Father|Husband|Partner 1
    SELECT c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary, n.surname, n.suffix, n.prefix, n.given, n.birthyear,
    n.deathyear, f.NAME, s.NAME, s.refnumber, s.actualtext, s.comments, c.refnumber,
    c.actualtext, c.comments
    FROM citationtable c
    LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
    LEFT OUTER JOIN eventtable e ON e.eventid=c.ownerid
    LEFT OUTER JOIN familytable fm ON e.ownerid=fm.familyid
    LEFT OUTER JOIN persontable p ON fm.fatherid=p.personid
    LEFT OUTER JOIN nametable n ON p.personid=n.ownerid
    LEFT OUTER JOIN facttypetable f ON e.eventtype=f.facttypeid
    WHERE c.ownertype=2 AND e.ownertype=1 AND f.ownertype=1 AND n.IsPrimary=1
    ;

    INSERT INTO tmpCitations
    -- Citations for Alternate Names
    SELECT c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, NOT n.IsPrimary, n.surname, n.suffix, n.prefix, n.given, n.birthyear,
    n.deathyear, 'Alternate Name' AS Citer, s.NAME AS Source, s.refnumber, s.actualtext, s.comments, c.refnumber,
    c.actualtext, c.comments
    FROM citationtable c
    LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
    LEFT OUTER JOIN nametable n ON n.nameid=c.ownerid
    WHERE c.ownertype=7 AND +n.IsPrimary=0
    ;
    CREATE INDEX tmpCitations_idx ON tmpCitations(CitID);
    -- Now filter the results to get rid of duplicate citation IDs due Alt Names
    SELECT * FROM tmpcitations
    WHERE uniq=1 OR uniq ISNULL
    ORDER BY RIN, Citer , SOURCE
    ;
    -- CitID
    -- RIN, Citer;
    -- END

    • Aha, one can edit a comment using the full editor which supports code blocks. Maybe one can even type the code in the simple editor… let’s see


      -- this is a double hyphen
      /*
      this is a multiline
      comment
      */

  7. I know everyone is busy with RM8 however how difficult would it be to add the place for each of the citations into a single column for the above query.

    After running the Query I can then set a filter to confirm by place if I have used a particular Source.

    Thanks in advance and this would be very helpful.

    Regards

    Wayne b

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.