Contents
CTE Versions for RM7 and RM8
Revised code by Pat Jones using common table expressions:
Version for RM7 and below AllCitations-RM7.sql tested 2021-01-20
Version for #RM8 and above AllCitations-RM8.sql revised 2022-04-27 to include duplicate uses
Unlike the old version, this script does not appear to be able to display “headless citations” or “phantom sources or citations” as Null fields as in the screenshot below from the original script from 2010.
Old version for RM7 and below
AllCitations.sql revised 2022-04-26 to correct a SQLite error detected by later versions of SQLite
Lists all citations in the database from which citations of non-existent sources (‘phantoms’) and citations for non-existent events or persons (‘headless’) can be found, along with other useful information such as all citations per source.
The query builds a temporary table with index which it then queries with a filter to suppress duplicate reports of citations against Alternate Names. While the query could be made without a temporary table, it would run exponentially more slowly with larger databases without invoking measures that are deprecated by SQLite documentation and incompatible with SQLite versions < 3.6.3. The intermediate table overcomes this problem and may be used for additional queries that will execute very quickly.
 |
| Screenshot from SQLiteSpy |
Revised the RM8 CTE version to include duplicate uses of a Citation.
Revised the old RM7 temp table version to avoid a SQLite error detected by later versions of SQLite.