Post by davidy13 on Sept 13, 2023 10:45:16 GMT
Hi, here is the code I tried to run below as a verbatim string with:
dbManager.Execute(verbatimString);
Unity gave me a "SQLiteException: near "WITH": syntax error". Is there a way to fix this? Did I make a mistake somewhere?
This code is supposed to look at similar tags in the IPC column and return pairs of objects that have similar tags.
This same SQL command runs fine using something like DB Browser for SQLite. But it does not seem to work with SimpleSQL.
dbManager.Execute(verbatimString);
Unity gave me a "SQLiteException: near "WITH": syntax error". Is there a way to fix this? Did I make a mistake somewhere?
This code is supposed to look at similar tags in the IPC column and return pairs of objects that have similar tags.
This same SQL command runs fine using something like DB Browser for SQLite. But it does not seem to work with SimpleSQL.
CREATE TEMP TABLE TempTable AS
WITH RECURSIVE SplitIPC AS (
SELECT
PatentId,
substr(IPC, 1, instr(IPC || '|', '|') - 1) AS tag,
substr(IPC || '|', instr(IPC || '|', '|') + 1) AS remainder
FROM
PatentInfo
UNION ALL
SELECT
PatentId,
substr(remainder, 1, instr(remainder || '|', '|') - 1),
substr(remainder, instr(remainder || '|', '|') + 1)
FROM
SplitIPC
WHERE
remainder != ''
),
Pairings AS (
SELECT DISTINCT
a.PatentId AS PatentId1,
b.PatentId AS PatentId2
FROM
SplitIPC a
JOIN
SplitIPC b
ON
a.tag = b.tag AND a.PatentId <> b.PatentId
)
SELECT
PatentId1,
GROUP_CONCAT(DISTINCT PatentId2) AS LinkedPatents
FROM
Pairings
GROUP BY
PatentId1;
UPDATE Year2021
SET LinkedPatents = (
SELECT LinkedPatents
FROM TempTable
WHERE Year2021.PatentId = TempTable.PatentId1
);
