using UDF in 'IN' clause

11 Nov 2005 11:25 PM

barcode

Hi,

I am desperate for some help on an issue I have with using an udf in a
select statement. The udf can return mulitple comma delimited values
e.g. 14123,12312

I am using the following (simplified) query:

select distinct firstname, lastname, number
from emp, numb
where emp_NUMBER=numb.emp_number
AND ('31335' in (dbo.getRefTopSector(emp.persid)))

This query keeps on running and running without any results.
I am pretty tired and I can't tell left from right now. Can someone
please point me in the right direction?

Much appreciated.

Pascal

12 Nov 2005 12:48 AM

David Gugick

barc***@dds.nl wrote:
Show quote

> Hi,
>
> I am desperate for some help on an issue I have with using an udf in a
> select statement. The udf can return mulitple comma delimited values
> e.g. 14123,12312
>
> I am using the following (simplified) query:
>
> select distinct firstname, lastname, number
> from emp, numb
> where emp_NUMBER=numb.emp_number
> AND ('31335' in (dbo.getRefTopSector(emp.persid)))
>
> This query keeps on running and running without any results.
> I am pretty tired and I can't tell left from right now. Can someone
> please point me in the right direction?
>
> Much appreciated.
>
> Pascal

I don't think that's valid syntax. Your function is returning a single
character string, not individual character strings. You could probably
use CHARINDEX to look for the string without much index help:

alter function dbo.test (@i int)
returns varchar(100)
as
begin
RETURN ',100,101,'
end

select dbo.test(1)

create table #test (col1 varchar(3))
insert into #test values ('100')
insert into #test values ('102')
select * from #test

Select * from #test where CHARINDEX(dbo.test(1), ',' + col1 + ',') > 0

col1
----
100

Drop Function dbo.test
Drop Table #test

--
David Gugick
Quest Software
www.imceda.com
www.quest.com

12 Nov 2005 12:50 AM

ML

Hard to say without seeing the function. Please post it.

ML

12 Nov 2005 1:14 AM

barcode

Thanks for all the help so far. Here's the function.

David- I am not sure what you mean. I am expecting a comma delimited
string that is put between the brackets of the in-clause. If I put them
in manually the query works fine. If I use the udf it goes on and
on........

----------------------------------------------------
CREATE FUNCTION [dbo].[getRefTopSector] ( @alfacode float)
RETURNS varchar(1000) AS
begin
DECLARE @List VARCHAR(8000)
SET @List = ''
IF (select distinct top 1 syn_rs from synoniem4 where syn_alfacode =
@alfacode) = 'S'
begin

        SELECT @List = @List + CAST(syn_topsector as VARCHAR(5)) + ','
        FROM synoniem4
        WHERE sun_code in (select syn_refcode from synoniem4 where
syn_alfacode = @alfacode)
        If len(@List) > 0
        Begin
        SET @List = Left(@List, Len(@List) - 1)
        End
        RETURN @List
end

ELSE
        SELECT @List = @List + CAST(syn_topsector as VARCHAR(5)) + ','
        FROM synoniem4
        where syn_alfacode = @alfacode
        If len(@List) > 0
        Begin
        SET @List = Left(@List, Len(@List) - 1)
        End
        RETURN @List
end
------------------------------------------------------------

12 Nov 2005 1:40 AM

--CELKO--

OH MY GOD!! A UDF to destroy First Normal Form, data integrity, the
foundations of declarative programming and common sense to fill in a
dynamic IN() predicate list.

Step away from the database and stop coding. After a few decades with
SQL, I can say this is some of the worst I have ever seen. You do not
understand SQL or RDBMS or declarative programmng at all.

I am not even going to try to clean it up because I am, pretty sure
that the DDL that you never bothered to post (bad netiquette!) is
probably screwed up beyond repair.

12 Nov 2005 1:47 AM

fnoppie

Ok, thanks for the encouraging and kind words. I came across some of
your other posts and mostly they seem to deal with downplaying the
poster and not providing any helpful comments. I am trying to learn
something and I am new at this so please bear with me.

Any other help would be greatly appreciated.

13 Nov 2005 4:57 AM

--CELKO--

>> Any other help would be greatly appreciated <<

Well, then you have to do your part. Please post DDL, so that people
do not have to guess what the keys, constraints, Declarative
Referential Integrity, data types, etc. in your schema are. Sample data
is also a good idea, along with clear specifications. It is very hard
to debug code when you do not let us see it.

Next, learn that SQL is a COMPILED DECLARATIVE language. You are
writing INTERPRETED PROCEDURAL code in the language. This is
fundamentally wrong. Writing a user defined function, using dynamic
SQL, denormalization are all signs that you have missed all of the
foundations of RDBMS and several basic programming concepts.

Good SQL programmers use joins, and set-oriented code. Warning! An
RDBMS is a whole, so if you screwed up the DDL, then the DML will be a
nightmare. But what people tend to do is look for a kludge and not a
solution.

Your function call should be a subquery, if you were writing SQL and
whatever your 3GL/OO language of choice is. Also, get copy of SQL
PROGRAMMING STYLE so you capitalization style will be consistent.

I have the bad habit of wanting to give solutions and not kludges.

13 Nov 2005 11:04 AM

barcode

Ok, I appreciate everyone's help on this issue. I will of course do my
part and post the DLL tomorrow, as I don't have access to the tables
right now. The problem is that I have to work with a table that isn't
normalized at all and for the process of normalization I needed the UDF
in the first place - to build a new table. So it's more a one kind
thing and not a query I have to run more often.

I understand now why the udf doesn't work in the in clause - thanks
David, Gert-Jan, Erland.

Celko, thanks for the info. I understand and I am a novice at this. I
prefer using joins but this is kind of an exception. Now I also know
what a kludge is ;)

Thanks.

Pascal

13 Nov 2005 1:58 PM

--CELKO--

>> . Now I also know what a kludge is ;) <<

Kludge or kluge:

n. Slang

1) A system, especially a computer system, that is constituted of
poorly matched elements or of elements originally intended for other
applications.

2) The use of undocumented, unintended, accidental or non-standard
features which appear in the software or hardware to solve an immediate
problem in a computer system.

3) A clumsy or inelegant solution to a problem.

Sources:

>From the old Scottish word "kludgie" meaning an outside toilet; A

Scottish engineering term for anything added in an ad hoc manner; the
spelling "kludge" adapted by American engineers in World War II.

"How to Design a Kludge", Jackson Granholme, Datamation, February 1962,
pp. 30-31], which defined it as "An ill-assorted collection of poorly
matching parts, forming a distressing whole."

It was beautiful, complex and wrong. In 150AD, Ptolemy of Alexandria
published his theory of epicycles--the idea that the moon, the sun and
the planets moved in circles which were moving in circles which were
moving in circles around the Earth. This theory explained the motion of
celestial objects to an astonishing degree of precision. It was,
however, what computer programmers call a kludge: a dirty, inelegant
solution. Some 1,500 years later, Johannes Kepler, a German astronomer,
replaced the whole complex edifice with three simple laws. -- The
Economist

12 Nov 2005 6:29 PM

Gert-Jan Strik

What David is trying to say, is that if your scalar UDF returns the
value '14123,12312,31335', then your query would read

select distinct firstname, lastname, number
from emp, numb
where emp_NUMBER=numb.emp_number
AND ('31335' in ('14123,12312,31335'))

which would not return any rows, since the list of the IN part only has
one value, which is not equal to '31335'.

I suggest you lose the UDF altogether, and simply add the logic to the
main query. I can't really give you a proper example, because your
current UDF is unreliable (you are using TOP 1 without ORDER BY clause),
and I can't seem to grasp the logic of the UDF. It definitely looks
unnormalized. I suggest you start by normalizing your schema.

HTH,
Gert-Jan

barc***@dds.nl wrote:
Show quote

>
> Thanks for all the help so far. Here's the function.
>
> David- I am not sure what you mean. I am expecting a comma delimited
> string that is put between the brackets of the in-clause. If I put them
> in manually the query works fine. If I use the udf it goes on and
> on........
>
> ----------------------------------------------------
> CREATE FUNCTION [dbo].[getRefTopSector] ( @alfacode float)
> RETURNS varchar(1000) AS
> begin
> DECLARE @List VARCHAR(8000)
> SET @List = ''
> IF (select distinct top 1 syn_rs from synoniem4 where syn_alfacode =
> @alfacode) = 'S'
> begin
>
>                 SELECT @List = @List + CAST(syn_topsector as VARCHAR(5)) + ','
>                 FROM synoniem4
>                 WHERE sun_code in (select syn_refcode from synoniem4 where
> syn_alfacode = @alfacode)
>                 If len(@List) > 0
>                 Begin
>                 SET @List = Left(@List, Len(@List) - 1)
>                 End
>                 RETURN @List
> end
>
> ELSE
>                 SELECT @List = @List + CAST(syn_topsector as VARCHAR(5)) + ','
>                 FROM synoniem4
>                 where syn_alfacode = @alfacode
>                 If len(@List) > 0
>                 Begin
>                 SET @List = Left(@List, Len(@List) - 1)
>                 End
>                 RETURN @List
> end
> ------------------------------------------------------------

12 Nov 2005 11:25 PM

ML

Err... I really think you should look closely at table-valued functions,
since they do exactly what you need.

There's a saying in my country that describes what you're doing - roughly
translated as: "reaching around your ass to get to your pocket".

Instead of simply joining sets of data you're building strings only to have
them parsed to extract something that should be a set, but it just simply is
not.

ML

12 Nov 2005 1:11 AM

jxstern

On 11 Nov 2005 15:25:04 -0800, barc***@dds.nl wrote:

>select distinct firstname, lastname, number
>from emp, numb
>where emp_NUMBER=numb.emp_number
>AND ('31335' in (dbo.getRefTopSector(emp.persid)))
>
>This query keeps on running and running without any results.
>I am pretty tired and I can't tell left from right now. Can someone
>please point me in the right direction?

try this:

select distinct firstname, lastname, number
from emp, numb
where emp_NUMBER=numb.emp_number
AND ('31335' in
(select yourfield from dbo.getRefTopSector(emp.persid)))

Sometimes SQLServer will accept invalid syntax and run off into the
weeds.

J.

12 Nov 2005 5:16 AM

Abhishek Pandey

Hi:
OP shdnt take CELKO's comment offensively. I understand its sometime
difficult to decode a bad coding and i believe CELKO's is just frustrated
with it (Maybe CELKO's shouldnt have bothered posting his comments)

And Ya I would agree with Jxstern. Somtimes SQL server 2k will run off into
infinite loop and you will end up searching for the reason somewhere else.

I think what you should be doing is to change your udf by doing a select
statement at the end (instead of just returning thing) and then do jxstern.

if the above doesnt work then let us know and post some sample data. Maybe
one of us can spend some more time and give you a better solution.

abhishek

AND ('31335' in
(select yourfield from dbo.getRefTopSector(emp.persid)))

Although there are no apparent reason why return shouldnt work but then i
would advise you to code the way as suggested by

Show quote

"jxstern" wrote:

> On 11 Nov 2005 15:25:04 -0800, barc***@dds.nl wrote:
> >select distinct firstname, lastname, number
> >from emp, numb
> >where emp_NUMBER=numb.emp_number
> >AND ('31335' in (dbo.getRefTopSector(emp.persid)))
> >
> >This query keeps on running and running without any results.
> >I am pretty tired and I can't tell left from right now. Can someone
> >please point me in the right direction?
>
>
> try this:
>
> select distinct firstname, lastname, number
> from emp, numb
> where emp_NUMBER=numb.emp_number
> AND ('31335' in
> (select yourfield from dbo.getRefTopSector(emp.persid)))
>
>
> Sometimes SQLServer will accept invalid syntax and run off into the
> weeds.
>
> J.
>
>

12 Nov 2005 12:54 PM

barcode

Hi Abhishek,

I tried your option but it didn't work. To be honest I am not quite
sure whether I implemented it correctly. I have spend way too much time
on this already and I am groing very frustrated. I want to try a
different approach and create a better base table. The table I have to
work with now was given to me and it's just not a good starting point.

Thanks to you all again.

12 Nov 2005 11:27 PM

ML

Ok. We are (well, I am, if no one else is) still ready to share the burdon of
your frustration, so please post DDL of the tables and some sample data. A
set-based solution can most certainly be achieved.

ML

12 Nov 2005 11:57 PM

Erland Sommarskog

Abhishek Pandey (AbhishekPan***@discussions.microsoft.com) writes:

> And Ya I would agree with Jxstern. Somtimes SQL server 2k will run off
> into infinite loop and you will end up searching for the reason
> somewhere else.

Maybe it does, but it has nothing to do with this. What Pascal has, is
perfectly legal syntax, he is not just understanding what it does. He
had:

   select distinct firstname, lastname, number
   from emp, numb
   where emp_NUMBER=numb.emp_number
   AND ('31335' in (dbo.getRefTopSector(emp.persid)))

Say his funtion returns '31355,4711,96563'. The this would have returned
a row:

   select distinct firstname, lastname, number
   from emp, numb
   where emp_NUMBER=numb.emp_number
   AND ('31335,4711,96563' in (dbo.getRefTopSector(emp.persid)))

Pascal incorrectly believed that the result from the function would
macgically be macro-expanded, but that does not happen. Fact is,
x IN (a, b, c) is just a syntactical shortcut for

   x = a OR A x = b OR x = c

and SQL Server very quickly rewrites an IN expresion this way internally,
which explains why often get multiple error messages with missing columns
with IN.

It's diffictult to say exactly how Pascal's code should look like, but a
simplified version would read:

   select distinct firstname, lastname, number
   from emp, numb
   where emp_NUMBER=numb.emp_number
      AND EXISTS (SELECT *
                  FROM synoniem4 s
                  WHERE s.syn_topsector = '31335'
                    AND syn_alfacode = emp.persid)

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

13 Nov 2005 5:32 PM

JXStern

On Sat, 12 Nov 2005 23:57:34 +0000 (UTC), Erland Sommarskog
<esq***@sommarskog.se> wrote:

>Maybe it does, but it has nothing to do with this. What Pascal has, is
>perfectly legal syntax, he is not just understanding what it does. He
>had:
>
>   select distinct firstname, lastname, number
>   from emp, numb
>   where emp_NUMBER=numb.emp_number
>   AND ('31335' in (dbo.getRefTopSector(emp.persid)))
>
>Say his funtion returns '31355,4711,96563'

I sort of assumed, probably incorrectly, that the UDF would return a
table (mostly because I've been working with a lot of such stuff
recently). If it returns a scalar, he should use an equality test, if
it returns a list, he could use something like a charindex.

The suggestions to first write the code without the UDF are probably
well taken.

J.