Using a UDF more than once in a query

Using a UDF more than once in a query

Post by Harlan Messinge » Sun, 12 Jan 2003 06:19:53



A table, MyTable, with primary key ObjectID (int 4), has a column sVariables
(text). The sVariables column contains a delimited list of key/value pairs
in the following format:

    key1,value1;key2;value2;...;

(Not my doing, no use suggesting that I normalize this!)

On some of my rows, one of the "keys" (or "variables") appears as
"title_0en" and another as "title_0fr", denoting the English and French
titles of the item represented by the row. 'title_0en,This is an
example;title_0fr,Ceci est un exemple;'

I created a user-defined function to parse out the value for a given
variable name,


varchar(50))
    RETURNS varchar(200) AS
    BEGIN











    BEGIN
     return ''
    END


4001)



    END

A complex query I created is meant to return both the English and the French
titles as separate columns, but it produced the English version in both
columns. A simplified version, leaving out the joined tables from the
complex one, looks like:

    SELECT
        PG.ObjectID ObjectID,
        cast(dbo.getD2GVariable(coalesce(PG.sVariables, ''), 'title_0en') as
varchar(100)) englishTitle,
        cast(dbo.getD2GVariable(coalesce(PG.sVariables, ''), 'title_0fr') as
varchar(100)) frenchTitle
    FROM        dbo.idune_100_uigchfcbnvvumtaecvbt_Objects PG
    WHERE PG.TypeID = 1040
    GROUP BY
        PG.ObjectID,
        cast(dbo.getD2GVariable(coalesce(PG.sVariables, ''), 'title_0en') as
varchar(100)),
        cast(dbo.getD2GVariable(coalesce(PG.sVariables, ''), 'title_0fr') as
varchar(100))
    ORDER BY
        PG.ObjectID

Same problem: both englishTitle and frenchTitle show me the English title.
Then, when I change 'title_0en' in the query to 'title_0fr', both columns
give me the French title, *whether or not I make the reverse change on the
following line*.

The following, ungrouped query DOES give the correct result:

    SELECT
    PG.ObjectID ObjectID,
    cast(dbo.getD2GVariable(coalesce(PG.sVariables, ''), 'title_0en') as
varchar(100)) englishTitle,
    cast(dbo.getD2GVariable(coalesce(PG.sVariables, ''), 'title_0fr') as
varchar(100)) frenchTitle
    FROM        dbo.idune_100_uigchfcbnvvumtaecvbt_Objects PG
    WHERE PG.TypeID = 1040
    ORDER BY
        PG.ObjectID

Is this a bug or a limitation in SQL Server or am I doing something
incorrect?

 
 
 

Using a UDF more than once in a query

Post by Steve Kas » Sun, 12 Jan 2003 07:04:39


Harlan,

  This is a known bug:
http://support.microsoft.com/default.aspx?scid=kb;en-us;323504

It sounds like you might be able to fix the problem by either reversing
the order of the
two parameters of your user-defined function or by making the first
parameter appear
different in the two calls, by doing something like this:

    SELECT
        PG.ObjectID ObjectID,
        cast(dbo.getD2GVariable(coalesce(left(PG.sVariables,4000), ''), 'title_0en') as
varchar(100)) englishTitle,
        cast(dbo.getD2GVariable(coalesce(PG.sVariables+space(0), ''), 'title_0fr') as
varchar(100)) frenchTitle
    FROM        dbo.idune_100_uigchfcbnvvumtaecvbt_Objects PG
    WHERE PG.TypeID = 1040
    GROUP BY
        PG.ObjectID,
        cast(dbo.getD2GVariable(coalesce(left(PG.sVariables,4000), ''), 'title_0en') as
varchar(100)),
        cast(dbo.getD2GVariable(coalesce(PG.sVariables+space(0), ''), 'title_0fr') as
varchar(100))
    ORDER BY
        PG.ObjectID*

SK
*


>A table, MyTable, with primary key ObjectID (int 4), has a column sVariables
>(text). The sVariables column contains a delimited list of key/value pairs
>in the following format:

>    key1,value1;key2;value2;...;

>(Not my doing, no use suggesting that I normalize this!)

>On some of my rows, one of the "keys" (or "variables") appears as
>"title_0en" and another as "title_0fr", denoting the English and French
>titles of the item represented by the row. 'title_0en,This is an
>example;title_0fr,Ceci est un exemple;'

>I created a user-defined function to parse out the value for a given
>variable name,


>varchar(50))
>    RETURNS varchar(200) AS
>    BEGIN











>    BEGIN
>     return ''
>    END


>4001)



>    END

>A complex query I created is meant to return both the English and the French
>titles as separate columns, but it produced the English version in both
>columns. A simplified version, leaving out the joined tables from the
>complex one, looks like:

>    SELECT
>        PG.ObjectID ObjectID,
>        cast(dbo.getD2GVariable(coalesce(PG.sVariables, ''), 'title_0en') as
>varchar(100)) englishTitle,
>        cast(dbo.getD2GVariable(coalesce(PG.sVariables, ''), 'title_0fr') as
>varchar(100)) frenchTitle
>    FROM        dbo.idune_100_uigchfcbnvvumtaecvbt_Objects PG
>    WHERE PG.TypeID = 1040
>    GROUP BY
>        PG.ObjectID,
>        cast(dbo.getD2GVariable(coalesce(PG.sVariables, ''), 'title_0en') as
>varchar(100)),
>        cast(dbo.getD2GVariable(coalesce(PG.sVariables, ''), 'title_0fr') as
>varchar(100))
>    ORDER BY
>        PG.ObjectID

>Same problem: both englishTitle and frenchTitle show me the English title.
>Then, when I change 'title_0en' in the query to 'title_0fr', both columns
>give me the French title, *whether or not I make the reverse change on the
>following line*.

>The following, ungrouped query DOES give the correct result:

>    SELECT
>    PG.ObjectID ObjectID,
>    cast(dbo.getD2GVariable(coalesce(PG.sVariables, ''), 'title_0en') as
>varchar(100)) englishTitle,
>    cast(dbo.getD2GVariable(coalesce(PG.sVariables, ''), 'title_0fr') as
>varchar(100)) frenchTitle
>    FROM        dbo.idune_100_uigchfcbnvvumtaecvbt_Objects PG
>    WHERE PG.TypeID = 1040
>    ORDER BY
>        PG.ObjectID

>Is this a bug or a limitation in SQL Server or am I doing something
>incorrect?


 
 
 

Using a UDF more than once in a query

Post by Steve Kas » Sun, 12 Jan 2003 07:00:33


Harlan,

  It sure looks like a bug to me.  Here's a simple repro - I'll try to
find out if
it's known or not, and report it if it is not.

create table T (
  x char(2)
)
insert into T values ('12')
insert into T values ('34')
GO

create function f (


) returns char(4) as begin


end

go

select
  dbo.f('ab',x),
  dbo.f('ab',reverse(x))
from T

select
  dbo.f('ab',x),
  dbo.f('ab',reverse(x))
from T
group by
  dbo.f('ab',x),
  dbo.f('ab',reverse(x))

go

drop function f
drop table T
go

Steve Kass
Drew University


>A table, MyTable, with primary key ObjectID (int 4), has a column sVariables
>(text). The sVariables column contains a delimited list of key/value pairs
>in the following format:

>    key1,value1;key2;value2;...;

>(Not my doing, no use suggesting that I normalize this!)

>On some of my rows, one of the "keys" (or "variables") appears as
>"title_0en" and another as "title_0fr", denoting the English and French
>titles of the item represented by the row. 'title_0en,This is an
>example;title_0fr,Ceci est un exemple;'

>I created a user-defined function to parse out the value for a given
>variable name,


>varchar(50))
>    RETURNS varchar(200) AS
>    BEGIN











>    BEGIN
>     return ''
>    END


>4001)



>    END

>A complex query I created is meant to return both the English and the French
>titles as separate columns, but it produced the English version in both
>columns. A simplified version, leaving out the joined tables from the
>complex one, looks like:

>    SELECT
>        PG.ObjectID ObjectID,
>        cast(dbo.getD2GVariable(coalesce(PG.sVariables, ''), 'title_0en') as
>varchar(100)) englishTitle,
>        cast(dbo.getD2GVariable(coalesce(PG.sVariables, ''), 'title_0fr') as
>varchar(100)) frenchTitle
>    FROM        dbo.idune_100_uigchfcbnvvumtaecvbt_Objects PG
>    WHERE PG.TypeID = 1040
>    GROUP BY
>        PG.ObjectID,
>        cast(dbo.getD2GVariable(coalesce(PG.sVariables, ''), 'title_0en') as
>varchar(100)),
>        cast(dbo.getD2GVariable(coalesce(PG.sVariables, ''), 'title_0fr') as
>varchar(100))
>    ORDER BY
>        PG.ObjectID

>Same problem: both englishTitle and frenchTitle show me the English title.
>Then, when I change 'title_0en' in the query to 'title_0fr', both columns
>give me the French title, *whether or not I make the reverse change on the
>following line*.

>The following, ungrouped query DOES give the correct result:

>    SELECT
>    PG.ObjectID ObjectID,
>    cast(dbo.getD2GVariable(coalesce(PG.sVariables, ''), 'title_0en') as
>varchar(100)) englishTitle,
>    cast(dbo.getD2GVariable(coalesce(PG.sVariables, ''), 'title_0fr') as
>varchar(100)) frenchTitle
>    FROM        dbo.idune_100_uigchfcbnvvumtaecvbt_Objects PG
>    WHERE PG.TypeID = 1040
>    ORDER BY
>        PG.ObjectID

>Is this a bug or a limitation in SQL Server or am I doing something
>incorrect?

 
 
 

Using a UDF more than once in a query

Post by Harlan Messinge » Sun, 12 Jan 2003 08:08:26


Steve, thank you so very much! (My first foray into UDFs, and wouldn't you
know it?)


> Harlan,

>   This is a known bug:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;323504

> It sounds like you might be able to fix the problem by either reversing
> the order of the
> two parameters of your user-defined function or by making the first
> parameter appear
> different in the two calls, by doing something like this:

>     SELECT
>         PG.ObjectID ObjectID,
>         cast(dbo.getD2GVariable(coalesce(left(PG.sVariables,4000), ''),
'title_0en') as
> varchar(100)) englishTitle,
>         cast(dbo.getD2GVariable(coalesce(PG.sVariables+space(0), ''),
'title_0fr') as
> varchar(100)) frenchTitle
>     FROM        dbo.idune_100_uigchfcbnvvumtaecvbt_Objects PG
>     WHERE PG.TypeID = 1040
>     GROUP BY
>         PG.ObjectID,
>         cast(dbo.getD2GVariable(coalesce(left(PG.sVariables,4000), ''),
'title_0en') as
> varchar(100)),
>         cast(dbo.getD2GVariable(coalesce(PG.sVariables+space(0), ''),
'title_0fr') as
> varchar(100))
>     ORDER BY
>         PG.ObjectID*

> SK
> *


> >A table, MyTable, with primary key ObjectID (int 4), has a column
sVariables
> >(text). The sVariables column contains a delimited list of key/value
pairs
> >in the following format:

> >    key1,value1;key2;value2;...;

> >(Not my doing, no use suggesting that I normalize this!)

> >On some of my rows, one of the "keys" (or "variables") appears as
> >"title_0en" and another as "title_0fr", denoting the English and French
> >titles of the item represented by the row. 'title_0en,This is an
> >example;title_0fr,Ceci est un exemple;'

> >I created a user-defined function to parse out the value for a given
> >variable name,


> >varchar(50))
> >    RETURNS varchar(200) AS
> >    BEGIN











> >    BEGIN
> >     return ''
> >    END


> >4001)



> >    END

> >A complex query I created is meant to return both the English and the
French
> >titles as separate columns, but it produced the English version in both
> >columns. A simplified version, leaving out the joined tables from the
> >complex one, looks like:

> >    SELECT
> >        PG.ObjectID ObjectID,
> >        cast(dbo.getD2GVariable(coalesce(PG.sVariables, ''), 'title_0en')
as
> >varchar(100)) englishTitle,
> >        cast(dbo.getD2GVariable(coalesce(PG.sVariables, ''), 'title_0fr')
as
> >varchar(100)) frenchTitle
> >    FROM        dbo.idune_100_uigchfcbnvvumtaecvbt_Objects PG
> >    WHERE PG.TypeID = 1040
> >    GROUP BY
> >        PG.ObjectID,
> >        cast(dbo.getD2GVariable(coalesce(PG.sVariables, ''), 'title_0en')
as
> >varchar(100)),
> >        cast(dbo.getD2GVariable(coalesce(PG.sVariables, ''), 'title_0fr')
as
> >varchar(100))
> >    ORDER BY
> >        PG.ObjectID

> >Same problem: both englishTitle and frenchTitle show me the English
title.
> >Then, when I change 'title_0en' in the query to 'title_0fr', both columns
> >give me the French title, *whether or not I make the reverse change on
the
> >following line*.

> >The following, ungrouped query DOES give the correct result:

> >    SELECT
> >    PG.ObjectID ObjectID,
> >    cast(dbo.getD2GVariable(coalesce(PG.sVariables, ''), 'title_0en') as
> >varchar(100)) englishTitle,
> >    cast(dbo.getD2GVariable(coalesce(PG.sVariables, ''), 'title_0fr') as
> >varchar(100)) frenchTitle
> >    FROM        dbo.idune_100_uigchfcbnvvumtaecvbt_Objects PG
> >    WHERE PG.TypeID = 1040
> >    ORDER BY
> >        PG.ObjectID

> >Is this a bug or a limitation in SQL Server or am I doing something
> >incorrect?

 
 
 

Using a UDF more than once in a query

Post by Steve Kas » Sun, 12 Jan 2003 09:00:32


You should be that lucky with the lottery.  Remind me to stay away from
you during lightning storms.  ;)

Steve


>Steve, thank you so very much! (My first foray into UDFs, and wouldn't you
>know it?)



>>Harlan,

>>  This is a known bug:
>>http://support.microsoft.com/default.aspx?scid=kb;en-us;323504

>>It sounds like you might be able to fix the problem by either reversing
>>the order of the
>>two parameters of your user-defined function or by making the first
>>parameter appear
>>different in the two calls, by doing something like this:

>>    SELECT
>>        PG.ObjectID ObjectID,
>>        cast(dbo.getD2GVariable(coalesce(left(PG.sVariables,4000), ''),

>'title_0en') as

>>varchar(100)) englishTitle,
>>        cast(dbo.getD2GVariable(coalesce(PG.sVariables+space(0), ''),

>'title_0fr') as

>>varchar(100)) frenchTitle
>>    FROM        dbo.idune_100_uigchfcbnvvumtaecvbt_Objects PG
>>    WHERE PG.TypeID = 1040
>>    GROUP BY
>>        PG.ObjectID,
>>        cast(dbo.getD2GVariable(coalesce(left(PG.sVariables,4000), ''),

>'title_0en') as

>>varchar(100)),
>>        cast(dbo.getD2GVariable(coalesce(PG.sVariables+space(0), ''),

>'title_0fr') as

>>varchar(100))
>>    ORDER BY
>>        PG.ObjectID*

>>SK
>>*


>>>A table, MyTable, with primary key ObjectID (int 4), has a column

>sVariables

>>>(text). The sVariables column contains a delimited list of key/value

>pairs

>>>in the following format:

>>>   key1,value1;key2;value2;...;

>>>(Not my doing, no use suggesting that I normalize this!)

>>>On some of my rows, one of the "keys" (or "variables") appears as
>>>"title_0en" and another as "title_0fr", denoting the English and French
>>>titles of the item represented by the row. 'title_0en,This is an
>>>example;title_0fr,Ceci est un exemple;'

>>>I created a user-defined function to parse out the value for a given
>>>variable name,


>>>varchar(50))
>>>   RETURNS varchar(200) AS
>>>   BEGIN











>>>   BEGIN
>>>    return ''
>>>   END


>>>4001)



>>>   END

>>>A complex query I created is meant to return both the English and the

>French

>>>titles as separate columns, but it produced the English version in both
>>>columns. A simplified version, leaving out the joined tables from the
>>>complex one, looks like:

>>>   SELECT
>>>       PG.ObjectID ObjectID,
>>>       cast(dbo.getD2GVariable(coalesce(PG.sVariables, ''), 'title_0en')

>as

>>>varchar(100)) englishTitle,
>>>       cast(dbo.getD2GVariable(coalesce(PG.sVariables, ''), 'title_0fr')

>as

>>>varchar(100)) frenchTitle
>>>   FROM        dbo.idune_100_uigchfcbnvvumtaecvbt_Objects PG
>>>   WHERE PG.TypeID = 1040
>>>   GROUP BY
>>>       PG.ObjectID,
>>>       cast(dbo.getD2GVariable(coalesce(PG.sVariables, ''), 'title_0en')

>as

>>>varchar(100)),
>>>       cast(dbo.getD2GVariable(coalesce(PG.sVariables, ''), 'title_0fr')

>as

>>>varchar(100))
>>>   ORDER BY
>>>       PG.ObjectID

>>>Same problem: both englishTitle and frenchTitle show me the English

>title.

>>>Then, when I change 'title_0en' in the query to 'title_0fr', both columns
>>>give me the French title, *whether or not I make the reverse change on

>the

>>>following line*.

>>>The following, ungrouped query DOES give the correct result:

>>>   SELECT
>>>   PG.ObjectID ObjectID,
>>>   cast(dbo.getD2GVariable(coalesce(PG.sVariables, ''), 'title_0en') as
>>>varchar(100)) englishTitle,
>>>   cast(dbo.getD2GVariable(coalesce(PG.sVariables, ''), 'title_0fr') as
>>>varchar(100)) frenchTitle
>>>   FROM        dbo.idune_100_uigchfcbnvvumtaecvbt_Objects PG
>>>   WHERE PG.TypeID = 1040
>>>   ORDER BY
>>>       PG.ObjectID

>>>Is this a bug or a limitation in SQL Server or am I doing something
>>>incorrect?

 
 
 

1. UDF used more than once

The account table uses a self-referencing 'Parent' field to create a
hierarchy of accounts that are named (Manager, Dealer, Associate,
Customer) and numbered (4, 3, 2, 1).  When a sale is made to a customer
I add columns to the Sales Analysis view to show who the Manager,
Dealer, and Associate are.

Because there are many places where I need to know who a customer's
Manager, Dealer, or Associate are, I created a UDF called
fn_AccountHierarchy that takes the name of any account and the desired
hierarchical level ('Manager', 'Dealer', or 'Associate') and it returns
the desired account.

So, I have a view that looks like this:

CREATE VIEW SalesAnalysis
SELECT
  'Manager' = dbo.fn_AccountHierarchy(Account, 'Manager'),
  'Dealer' = dbo.fn_AccountHierarchy(Account, 'Dealer'),
  'Associate' = dbo.fn_AccountHierarchy(Account, 'Associate'),
  'Customer' = Account,
  Sales
FROM SomeJoinedTables

This works fine, but when I summarize it:

SELECT Manager, Dealer, Associate, Customer, SUM(Sales)
  FROM SalesAnalysis
  GROUP BY Manager, Dealer, Associate, Customer
  ORDER BY Manager, Dealer, Associate, Customer

suddenly the first 4 columns have identical accounts in them, all being
the Manager.  That is, the Dealer, Associate, and Customer columns are
changed from the correct values that were in the SalesAnalysis view to
be just the value from the Manager column, repeated 3 times.

If you try to put it all in the SalesAnalysis view like this:

CREATE VIEW SalesAnalysis
SELECT
  'Manager' = dbo.fn_AccountHierarchy(Account, 'Manager'),
  'Dealer' = dbo.fn_AccountHierarchy(Account, 'Dealer'),
  'Associate' = dbo.fn_AccountHierarchy(Account, 'Associate'),
  'Customer' = Account,
  SUM(Sales)
FROM SomeJoinedTables
GROUP BY dbo.fn_AccountHierarchy(Account, 'Manager'),
   dbo.fn_AccountHierarchy(Account, 'Dealer'),
   dbo.fn_AccountHierarchy(Account, 'Associate'),
   Account

the malfunction is identical.  However, any one of the GROUP BY portions
works fine by itself:

CREATE VIEW SalesAnalysis
SELECT
  'Dealer' = dbo.fn_AccountHierarchy(Account, 'Dealer'),
  SUM(Sales)
FROM SomeJoinedTables
GROUP BY dbo.fn_AccountHierarchy(Account, 'Dealer'),

works correctly.

It seems like the new UDFs aren't fully compatible with the rest of SQL
functionality, especially the GROUP BY clause used with aggregates.
Anyone else experienced this?

Tom Ellison

2. Database Schema options....Need some advice

3. Query using UDF joined to a sub query vs Temp Table to sub Query

4. Powerbuilder or SQLWindows mistake

5. UDF problem using If-Else in simple UDF

6. Nationwide-253200--ORACLE Financials-Accounting Applications-Senior Project Accounting Consultant

7. xpath query against UDF with UDF parameters

8. Old Project won't run on new hard drive?

9. Using a udf in a query

10. Problem querying more than once.....

11. unable to connect to db using odbc once ado 2.6 is installed

12. Referencing a parameter more than once in a query

13. how to delete a once used control