Using the result of a function in the retrned values & where-clause

Question

Post reply

Using the result of a function in the retrned values & where-clause

tsmets

Valued Member

Points: 51
More actions
May 6, 2010 at 10:20 am

#218648

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,

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

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;

tsmets Valued Member Points: 51 More actions · Answer 2

This might be a dumb question but why do you want to have the column names specified ... ?

\T,

Ben Teraberry SSChampion Points: 11845 More actions · Answer 3

Generally it's bad form to use *. Specify the column names so you don't end up having issues in the future.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Jeff Moden SSC Guru Points: 1003926 More actions · Answer 4

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

tsmets Valued Member Points: 51 More actions · Answer 5

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,