Why can you not use it in a where clause?
On Friday, 3 April 2015, Harshad RJ <[email protected]> wrote:
> 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]
> <javascript:_e(%7B%7D,'cvml','h2-database%[email protected]');>
> .
> To post to this group, send email to [email protected]
> <javascript:_e(%7B%7D,'cvml','[email protected]');>.
> Visit this group at http://groups.google.com/group/h2-database.
> For more options, visit https://groups.google.com/d/optout.
>
--
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.
