Hello Dan,
sorry it is just like:
CREATE VIRTUAL TABLE if not exists OCR USING fts4(content="", `FullOCR`);
&
CREATE TABLE `ART` (
`ID` integer NOT NULL,
`Kundennummer` integer,
`Rechnungsnummer` varchar,
`Rechnungsdatum` datetime,
`PDF` varchar,
PRIMARY KEY(ID)
);
And the "FullOCR" content is:
Row1: "WORD01"
"WORD02"
.
.
Row2: "WORD01"
"WORD02"
"framework"
.
.
I have inserted only 9 rows into "FullOCR" and maybe 4 into "ART".
Have one matching ID = docid = 2 to test "JOIN".
But you can use just any 2 tables and try to do a "JOIN" or "COUNT"
operation.
"SELECT COUNT(*) FROM OCR;" produces error so my workaround war using
"OCR_docsize" in place of "OCR"
"SELECT docid FROM OCR WHERE FullOCR MATCH 'framework';" gives correctly one
row with the docid = 2.
but when I try to use this in a "JOIN" statement produces error and no
workaround with "OCR_docsize" gives correct result.
BR,
Marta
-----Original Message-----
From: Dan Kennedy
Sent: Saturday, May 23, 2015 9:34 AM
To: sqlite-users at mailinglists.sqlite.org
Subject: Re: [sqlite] Contentless FTS4 Tables
On 05/23/2015 04:33 AM, ShadowMarta at yahoo.de wrote:
> Hi!
>
> I have made a Contentless FTS4 Table like:
>
> "CREATE VIRTUAL TABLE if not exists OCR USING fts4(content="", `FullOCR`
> varchar;"
>
> And managed to insert some data into it.
>
> I have 2 questions:
>
> 1.) How to get the proper COUNT on the table ?
> The only query seems to work is:
> "SELECT COUNT(*) FROM OCR_docsize;" is this the right way to do it
> ?
>
> 2.) How to perform a "JOIN" operation with it?
> "SELECT docid FROM OCR WHERE FullOCR MATCH 'framework';" result is
> "2".
>
> "SELECT * FROM ART INNER JOIN OCR ON ART.ID = (SELECT docid FROM OCR
> WHERE FullOCR MATCH 'framework') ORDER BY ID;"
> Gives "SQL logic error or missing database:"
>
> "SELECT * FROM ART INNER JOIN OCR_docsize ON ART.ID = (SELECT docid
> FROM OCR WHERE FullOCR MATCH 'framework') ORDER BY ID;"
> Gives me 9 results back - should be only 1 -
>
> "SELECT * FROM ART NATURAL JOIN (SELECT docid FROM OCR WHERE FullOCR
> MATCH 'framework') ORDER BY ID;"
> Gives me 9 results back - should be only 1 -
>
> What I am missing here?
Are you able to make the database file available for download somewhere?
Thanks,
Dan.
_______________________________________________
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
