| Query Name | Description | File |
|---|---|---|
| blankname_in_addresslist | List Persons with Blank Names in the Address List — (a fault that may occur in a GEDCOM import). | RM4_Queries.sql |
| selected_surnames | List Persons with specified Surnames. — Example of creating a SQL View or Virtual Table and the explicit use of COLLATE NOCASE to override the RMNOCASE collation defined for certain fields and embedded in the RootsMagic application. | |
| PlacesDetails | Lists Places having Place Details | |
| UnusedPlaces | List of unused Places |
The queries above are included in the one file. Some SQLite managers can import a query file and some can also export a query file (e.g. SQLiteman does both). Others may require you to open the file with a text editor and copy/paste the query into the SQLite manager’s query editor. With SQLiteman, you place the cursor anywhere in a query line and that is the one executed; SQLiteSpy requires you to highlight the selected command and execute using Ctrl+F9 rather than F9 which would run the whole listing. Each query must end with a semi-colon to demark it from the others.
Optionally, the queries can be expanded to include the CREATE VIEW command by deleting the semicolon at the end of its line. When successfully executed, the query is embedded in the database as a virtual table which remains with the database file until it is dropped (deleted). Some SQLite managers allow you to save a query as a view from the user interface, putting a GUI in front an internal CREATE VIEW.
I am running Acces 2010 and when I copy and paste the following, I get these errors.
— List Persons with Blank Names in the Address List
— (a fault that may occur in a GEDCOM import).
— Drop the last NOT to see all other persons with addresses.
— TomH 14 Dec 2009
CREATE VIEW blankname_in_addresslist AS;
SELECT Surname||’, ‘||Given COLLATE NOCASE AS Person, Adr.Name COLLATE NOCASE AS AddressName,
Adr.Street1, Adr.Street2, Adr.City, Adr.State, Adr.Country, Adr.Phone1, Adr.Fax, Adr.Email
FROM NameTable AS Nam, AddressLinkTable AS Lnk, AddressTable AS Adr
WHERE Nam.OwnerID = Lnk.OwnerID AND Lnk.AddressID = Adr.AddressID
AND Adr.AddressType = 0 AND Adr.Name NOT LIKE ‘%_’;
ERROR: Invalid SQL statement;expected ‘DELETE’, ‘INSERT’, ‘SELECT’, pr ‘UPDATE’.
I also get a SYNTAX error on some of the querries. Am I doing something wrong?
should not end with a semi-colon. It is not a complete statement without flowing onto the following SELECT.
Alternatively, as I’m unsure how SQLite Views are handled by MS Access, delete the CREATE statement, leaving only the SELECT statement.
As to SYNTAX errors, I don’t use MS Access so I’m unsure whether the syntax checking is on the part of Access or SQlite.
Tom