Hi,
I have created a custom function called SIMILARITY to measure similarity
between two strings. It works fine when I use it like this:
SELECT name, SIMILARITY(name, 'alex') AS siml
FROM Person
ORDER BY siml DESC
LIMIT 10;
However, when querying a large table, this is slow. To speed it up, I want
to filter away names whose similarity is very low (so that result set would
be smaller and sorting would be faster). I am not allowed to use the siml
alias in the WHERE clause, so I tried using a CTE:
WITH cte(name, siml) AS (
SELECT name, SIMILARITY(name, 'alex') AS siml
FROM Person
)
SELECT * FROM cte
WHERE cte.siml > 0.1
ORDER BY cte.siml DESC
LIMIT 10;
This syntax works fine in Postgresql. But in H2, it gives this error:
*Syntax error in SQL statement "recursive queries without UNION ALL";
expected {1};*
>From the past messages in this group, it seems like WITH statement is not
yet fully supported. But this seems to be a simple variant. Just wondering
if there is a quick solution possible.
Thanks,
Harshad
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
