May 6, 2010 at 10:20 am
I have a function that calculates the distance between 2 STRINGs (See Levenshtein implementation).
This works find :
select dbo.LEVENSHTEIN (USEARCH.firstname, ?) distance, *
from IDM.USER_SEARCH USEARCH
but as usual, the result of the of distance is not available to be included in the where clause.
So the question is :
How do I have the result of soemthing like :
select
dbo.LEVENSHTEIN (USEARCH.firstname, ?) distance, *
from
IDM.USER_SEARCH USEARCH
where
distance < 20 -- not available
order by
distance asc -- not available
Tx,
\T,
May 6, 2010 at 10:46 am
Try this:
with Search_cte as ( -- name the CTE appropriately
select
dbo.LEVENSHTEIN (USEARCH.firstname, ?) distance,
* -- should really specify the columns
from
IDM.USER_SEARCH USEARCH
)
select
* -- specify columns here, not *
from
Search_cte -- use the cte name you use here
where
distance < 20
order by
distance asc;
May 6, 2010 at 2:37 pm
This might be a dumb question but why do you want to have the column names specified ... ?
\T,
May 6, 2010 at 4:33 pm
May 6, 2010 at 8:05 pm
Although it's a bad practice to use "*", it's not really going to matter here. Sargeability has already been destroyed by the function and it's not a view that will need to be recompiled if the table ever changes.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 7, 2010 at 4:04 am
OK ...
Thus beside good programming practice there is no technical reason that we could not use "*" in the query.
Another question (also posted on the ad-hoc forum):
Any idea on how to have this working in Hibernate ....
I would like to avoid using Stored Procs or Functions but apparently Hibernate (please note : Hibernate and not NHibernate) does not like the syntaxe ...
03:16:52,384 ERROR PARSER:33 - line 1:1: unexpected token: with
Exception in thread "main" java.lang.IllegalArgumentException: node to traverse cannot be null!
at org.hibernate.hql.ast.util.NodeTraverser.traverseDepthFirst(NodeTraverser.java:31)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:254)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:157)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111)
If some one knows how to go around this ?
\T,
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply