Computed columns in temp tables

I am having a problem with using UDF as part of a temp table computed
column. Here's the sample code:
IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name =
'fn_test')
DROP FUNCTION dbo.fn_test
GO
CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
RETURNS INT AS
BEGIN
DECLARE @.z INT
SET @.z = @.x + @.y
RETURN @.z
END
GO
CREATE TABLE #X
(
x INT,
y INT,
z AS (dbo.fn_test(x,y))
)
I receive the following error:
Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.fn_test'.
I do not get this error if I use a regular table.
HELP!
Looks like the UDF is being looked up in the tempdb database. If you created
the UDF in tempdb, your table creation will succeed. I am not sure if this
is the expected behavior. I'll post again, if I find out more.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
news:eZChvYqWEHA.2804@.TK2MSFTNGP10.phx.gbl...
I am having a problem with using UDF as part of a temp table computed
column. Here's the sample code:
IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name =
'fn_test')
DROP FUNCTION dbo.fn_test
GO
CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
RETURNS INT AS
BEGIN
DECLARE @.z INT
SET @.z = @.x + @.y
RETURN @.z
END
GO
CREATE TABLE #X
(
x INT,
y INT,
z AS (dbo.fn_test(x,y))
)
I receive the following error:
Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.fn_test'.
I do not get this error if I use a regular table.
HELP!
|||Hi,
As far as I know, You cannot use a UDF inside the table creation. It should
be
create table #x(i int,j int,k as i+j)
insert into #x(i,j) values(1,10)
select * from #x
output will be:-
i j k
-- -- --
1 10 11
(1 row(s) affected)
Thanks
Hari
MCDBA
"Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
news:eZChvYqWEHA.2804@.TK2MSFTNGP10.phx.gbl...
> I am having a problem with using UDF as part of a temp table computed
> column. Here's the sample code:
> IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name =
> 'fn_test')
> DROP FUNCTION dbo.fn_test
> GO
> CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
> RETURNS INT AS
> BEGIN
> DECLARE @.z INT
> SET @.z = @.x + @.y
> RETURN @.z
> END
> GO
> CREATE TABLE #X
> (
> x INT,
> y INT,
> z AS (dbo.fn_test(x,y))
> )
> I receive the following error:
> Server: Msg 208, Level 16, State 1, Line 2
> Invalid object name 'dbo.fn_test'.
> I do not get this error if I use a regular table.
> HELP!
>
|||Sorry for the wrong information.
Thanks Vyas. I have never tried this option.
Thanks
Hari
MCDBA
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:OLTxVgqWEHA.716@.TK2MSFTNGP11.phx.gbl...
> Hi,
> As far as I know, You cannot use a UDF inside the table creation. It
should[vbcol=seagreen]
> be
> create table #x(i int,j int,k as i+j)
> insert into #x(i,j) values(1,10)
> select * from #x
> output will be:-
> i j k
> -- -- --
> 1 10 11
> (1 row(s) affected)
>
> --
> Thanks
> Hari
> MCDBA
> "Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
> news:eZChvYqWEHA.2804@.TK2MSFTNGP10.phx.gbl...
=
>
|||I have thought that was the case but no luck! I modified the code to
explicitly call the function in the database where it was created and still
got the same error. Here's modified code:
use master
GO
IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name =
'fn_test')
DROP FUNCTION dbo.fn_test
GO
CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
RETURNS INT AS
BEGIN
RETURN @.x + @.y
END
GO
CREATE TABLE #X
(
x INT,
y INT,
z AS (master.dbo.fn_test(x,y))
)
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:O52L6fqWEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Looks like the UDF is being looked up in the tempdb database. If you
created
> the UDF in tempdb, your table creation will succeed. I am not sure if this
> is the expected behavior. I'll post again, if I find out more.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
> news:eZChvYqWEHA.2804@.TK2MSFTNGP10.phx.gbl...
> I am having a problem with using UDF as part of a temp table computed
> column. Here's the sample code:
> IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name =
> 'fn_test')
> DROP FUNCTION dbo.fn_test
> GO
> CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
> RETURNS INT AS
> BEGIN
> DECLARE @.z INT
> SET @.z = @.x + @.y
> RETURN @.z
> END
> GO
> CREATE TABLE #X
> (
> x INT,
> y INT,
> z AS (dbo.fn_test(x,y))
> )
> I receive the following error:
> Server: Msg 208, Level 16, State 1, Line 2
> Invalid object name 'dbo.fn_test'.
> I do not get this error if I use a regular table.
> HELP!
>
>
|||As Vyas pointed out, the issue is that you have to create the function in
tempdb. I think he is right in suggesting that you can't use UDFs that
reside in a different database in the definition of a table.
Jacco Schalkwijk
SQL Server MVP
"Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
news:%23siqQFrWEHA.3512@.TK2MSFTNGP12.phx.gbl...
> I have thought that was the case but no luck! I modified the code to
> explicitly call the function in the database where it was created and
still[vbcol=seagreen]
> got the same error. Here's modified code:
> use master
> GO
> IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name =
> 'fn_test')
> DROP FUNCTION dbo.fn_test
> GO
> CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
> RETURNS INT AS
> BEGIN
> RETURN @.x + @.y
> END
> GO
> CREATE TABLE #X
> (
> x INT,
> y INT,
> z AS (master.dbo.fn_test(x,y))
> )
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:O52L6fqWEHA.2544@.TK2MSFTNGP10.phx.gbl...
> created
this[vbcol=seagreen]
=
>
|||Hi,
temporary tables are always made in tempdb (and only tempdb for your solution)
change
use master
on
use tempdb
Regards,
Pablo
"Steven Yampolsky" wrote:

> I have thought that was the case but no luck! I modified the code to
> explicitly call the function in the database where it was created and still
> got the same error. Here's modified code:
> use master
> GO
> IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name =
> 'fn_test')
> DROP FUNCTION dbo.fn_test
> GO
> CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
> RETURNS INT AS
> BEGIN
> RETURN @.x + @.y
> END
> GO
> CREATE TABLE #X
> (
> x INT,
> y INT,
> z AS (master.dbo.fn_test(x,y))
> )
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:O52L6fqWEHA.2544@.TK2MSFTNGP10.phx.gbl...
> created
>
>
|||How can I create a function in tempdb while inside a stored procedure? I
don't think I can do that.
Lets assume I can use sp_executesql to create the function inside a SP. What
will be a lifespan of it? Will it get dropped once the session is closed?
will it be accessible from other sessions? Will there be a name conflict
when two sessions will try and execute the same SP?
The more I get into it, the more questions I get. Hopefully I'll come out
with good knowledge!
Steve
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:uCXtqSrWEHA.2520@.TK2MSFTNGP12.phx.gbl...
> As Vyas pointed out, the issue is that you have to create the function in
> tempdb. I think he is right in suggesting that you can't use UDFs that
> reside in a different database in the definition of a table.
>
|||How can I create a function in tempdb while inside a stored procedure? I
don't think I can do that.
Lets assume I can use sp_executesql to create the function inside a SP. What
will be a lifespan of it? Will it get dropped once the session is closed?
will it be accessible from other sessions? Will there be a name conflict
when two sessions will try and execute the same SP?
The more I get into it, the more questions I get. Hopefully I'll come out
with good knowledge!
Steve
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:uCXtqSrWEHA.2520@.TK2MSFTNGP12.phx.gbl...
> As Vyas pointed out, the issue is that you have to create the function in
> tempdb. I think he is right in suggesting that you can't use UDFs that
> reside in a different database in the definition of a table.
>
|||Create a permanent function in tempdb, like
USE tempdb
GO
CREATE FUNCTION dbo.fnTest
(@.x int, @.y int)
RETURNS int
AS
BEGIN
RETURN (@.x+@.y)
END
GO
Now using another database u can call this function during temp table
creation
eg.
USE Pubs
CREATE TABLE #temp(x int,y int, z as dbo.fnTest(x,y))
INSERT INTO #temp VALUES(1,2)
INSERT INTO #temp VALUES(3,2)
SELECT * FROM #temp
Roji. P. Thomas
SQL Server Programmer
"Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
news:eLwmCwRXEHA.2940@.TK2MSFTNGP09.phx.gbl...
> How can I create a function in tempdb while inside a stored procedure? I
> don't think I can do that.
> Lets assume I can use sp_executesql to create the function inside a SP.
What
> will be a lifespan of it? Will it get dropped once the session is closed?
> will it be accessible from other sessions? Will there be a name conflict
> when two sessions will try and execute the same SP?
> The more I get into it, the more questions I get. Hopefully I'll come out
> with good knowledge!
> Steve
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid >
wrote[vbcol=seagreen]
> in message news:uCXtqSrWEHA.2520@.TK2MSFTNGP12.phx.gbl...
in
>

Computed columns in temp tables

I am having a problem with using UDF as part of a temp table computed
column. Here's the sample code:
IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name =
'fn_test')
DROP FUNCTION dbo.fn_test
GO
CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
RETURNS INT AS
BEGIN
DECLARE @.z INT
SET @.z = @.x + @.y
RETURN @.z
END
GO
CREATE TABLE #X
(
x INT,
y INT,
z AS (dbo.fn_test(x,y))
)
I receive the following error:
Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.fn_test'.
I do not get this error if I use a regular table.
HELP!Looks like the UDF is being looked up in the tempdb database. If you created
the UDF in tempdb, your table creation will succeed. I am not sure if this
is the expected behavior. I'll post again, if I find out more.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
news:eZChvYqWEHA.2804@.TK2MSFTNGP10.phx.gbl...
I am having a problem with using UDF as part of a temp table computed
column. Here's the sample code:
IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name =
'fn_test')
DROP FUNCTION dbo.fn_test
GO
CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
RETURNS INT AS
BEGIN
DECLARE @.z INT
SET @.z = @.x + @.y
RETURN @.z
END
GO
CREATE TABLE #X
(
x INT,
y INT,
z AS (dbo.fn_test(x,y))
)
I receive the following error:
Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.fn_test'.
I do not get this error if I use a regular table.
HELP!|||Hi,
As far as I know, You cannot use a UDF inside the table creation. It should
be
create table #x(i int,j int,k as i+j)
insert into #x(i,j) values(1,10)
select * from #x
output will be:-
i j k
-- -- --
1 10 11
(1 row(s) affected)
Thanks
Hari
MCDBA
"Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
news:eZChvYqWEHA.2804@.TK2MSFTNGP10.phx.gbl...
> I am having a problem with using UDF as part of a temp table computed
> column. Here's the sample code:
> IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name =
> 'fn_test')
> DROP FUNCTION dbo.fn_test
> GO
> CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
> RETURNS INT AS
> BEGIN
> DECLARE @.z INT
> SET @.z = @.x + @.y
> RETURN @.z
> END
> GO
> CREATE TABLE #X
> (
> x INT,
> y INT,
> z AS (dbo.fn_test(x,y))
> )
> I receive the following error:
> Server: Msg 208, Level 16, State 1, Line 2
> Invalid object name 'dbo.fn_test'.
> I do not get this error if I use a regular table.
> HELP!
>|||Sorry for the wrong information.
Thanks Vyas. I have never tried this option.
Thanks
Hari
MCDBA
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:OLTxVgqWEHA.716@.TK2MSFTNGP11.phx.gbl...
> Hi,
> As far as I know, You cannot use a UDF inside the table creation. It
should
> be
> create table #x(i int,j int,k as i+j)
> insert into #x(i,j) values(1,10)
> select * from #x
> output will be:-
> i j k
> -- -- --
> 1 10 11
> (1 row(s) affected)
>
> --
> Thanks
> Hari
> MCDBA
> "Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
> news:eZChvYqWEHA.2804@.TK2MSFTNGP10.phx.gbl...
=[vbcol=seagreen]
>|||I have thought that was the case but no luck! I modified the code to
explicitly call the function in the database where it was created and still
got the same error. Here's modified code:
use master
GO
IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name =
'fn_test')
DROP FUNCTION dbo.fn_test
GO
CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
RETURNS INT AS
BEGIN
RETURN @.x + @.y
END
GO
CREATE TABLE #X
(
x INT,
y INT,
z AS (master.dbo.fn_test(x,y))
)
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:O52L6fqWEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Looks like the UDF is being looked up in the tempdb database. If you
created
> the UDF in tempdb, your table creation will succeed. I am not sure if this
> is the expected behavior. I'll post again, if I find out more.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
> news:eZChvYqWEHA.2804@.TK2MSFTNGP10.phx.gbl...
> I am having a problem with using UDF as part of a temp table computed
> column. Here's the sample code:
> IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name =
> 'fn_test')
> DROP FUNCTION dbo.fn_test
> GO
> CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
> RETURNS INT AS
> BEGIN
> DECLARE @.z INT
> SET @.z = @.x + @.y
> RETURN @.z
> END
> GO
> CREATE TABLE #X
> (
> x INT,
> y INT,
> z AS (dbo.fn_test(x,y))
> )
> I receive the following error:
> Server: Msg 208, Level 16, State 1, Line 2
> Invalid object name 'dbo.fn_test'.
> I do not get this error if I use a regular table.
> HELP!
>
>|||As Vyas pointed out, the issue is that you have to create the function in
tempdb. I think he is right in suggesting that you can't use UDFs that
reside in a different database in the definition of a table.
Jacco Schalkwijk
SQL Server MVP
"Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
news:%23siqQFrWEHA.3512@.TK2MSFTNGP12.phx.gbl...
> I have thought that was the case but no luck! I modified the code to
> explicitly call the function in the database where it was created and
still
> got the same error. Here's modified code:
> use master
> GO
> IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name =
> 'fn_test')
> DROP FUNCTION dbo.fn_test
> GO
> CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
> RETURNS INT AS
> BEGIN
> RETURN @.x + @.y
> END
> GO
> CREATE TABLE #X
> (
> x INT,
> y INT,
> z AS (master.dbo.fn_test(x,y))
> )
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:O52L6fqWEHA.2544@.TK2MSFTNGP10.phx.gbl...
> created
this[vbcol=seagreen]
=[vbcol=seagreen]
>|||Hi,
temporary tables are always made in tempdb (and only tempdb for your solutio
n)
change
use master
on
use tempdb
Regards,
Pablo
"Steven Yampolsky" wrote:

> I have thought that was the case but no luck! I modified the code to
> explicitly call the function in the database where it was created and stil
l
> got the same error. Here's modified code:
> use master
> GO
> IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name =
> 'fn_test')
> DROP FUNCTION dbo.fn_test
> GO
> CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
> RETURNS INT AS
> BEGIN
> RETURN @.x + @.y
> END
> GO
> CREATE TABLE #X
> (
> x INT,
> y INT,
> z AS (master.dbo.fn_test(x,y))
> )
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:O52L6fqWEHA.2544@.TK2MSFTNGP10.phx.gbl...
> created
>
>|||How can I create a function in tempdb while inside a stored procedure? I
don't think I can do that.
Lets assume I can use sp_executesql to create the function inside a SP. What
will be a lifespan of it? Will it get dropped once the session is closed?
will it be accessible from other sessions? Will there be a name conflict
when two sessions will try and execute the same SP?
The more I get into it, the more questions I get. Hopefully I'll come out
with good knowledge!
Steve
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:uCXtqSrWEHA.2520@.TK2MSFTNGP12.phx.gbl...
> As Vyas pointed out, the issue is that you have to create the function in
> tempdb. I think he is right in suggesting that you can't use UDFs that
> reside in a different database in the definition of a table.
>|||Create a permanent function in tempdb, like
USE tempdb
GO
CREATE FUNCTION dbo.fnTest
(@.x int, @.y int)
RETURNS int
AS
BEGIN
RETURN (@.x+@.y)
END
GO
Now using another database u can call this function during temp table
creation
eg.
USE Pubs
CREATE TABLE #temp(x int,y int, z as dbo.fnTest(x,y))
INSERT INTO #temp VALUES(1,2)
INSERT INTO #temp VALUES(3,2)
SELECT * FROM #temp
Roji. P. Thomas
SQL Server Programmer
"Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
news:eLwmCwRXEHA.2940@.TK2MSFTNGP09.phx.gbl...
> How can I create a function in tempdb while inside a stored procedure? I
> don't think I can do that.
> Lets assume I can use sp_executesql to create the function inside a SP.
What
> will be a lifespan of it? Will it get dropped once the session is closed?
> will it be accessible from other sessions? Will there be a name conflict
> when two sessions will try and execute the same SP?
> The more I get into it, the more questions I get. Hopefully I'll come out
> with good knowledge!
> Steve
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
wrote
> in message news:uCXtqSrWEHA.2520@.TK2MSFTNGP12.phx.gbl...
in[vbcol=seagreen]
>|||Won't that function get dropped/cleaned up and will require constant
checking for its existance?
Steve
"Roji. P. Thomas" <lazydragon@.nowhere.com> wrote in message
news:e5MzPCSXEHA.3016@.tk2msftngp13.phx.gbl...
> Create a permanent function in tempdb, like
> USE tempdb
> GO
>
> CREATE FUNCTION dbo.fnTest
> (@.x int, @.y int)
> RETURNS int
> AS
> BEGIN
> RETURN (@.x+@.y)
> END
> GO
> Now using another database u can call this function during temp table
> creation
> eg.
>
> USE Pubs
>
> CREATE TABLE #temp(x int,y int, z as dbo.fnTest(x,y))
>
> INSERT INTO #temp VALUES(1,2)
> INSERT INTO #temp VALUES(3,2)
> SELECT * FROM #temp
>
> --
> Roji. P. Thomas
> SQL Server Programmer
> "Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
> news:eLwmCwRXEHA.2940@.TK2MSFTNGP09.phx.gbl...
> What
closed?[vbcol=seagreen]
out[vbcol=seagreen]
> wrote
> in
>

Computed columns in temp tables

I am having a problem with using UDF as part of a temp table computed column. Here's the sample code:
IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name = 'fn_test')
DROP FUNCTION dbo.fn_testGO
CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
RETURNS INT AS
BEGIN
DECLARE @.z INT
SET @.z = @.x + @.y
RETURN @.z
END
GO

CREATE TABLE #X
(
x INT,
y INT,
z AS (dbo.fn_test(x,y))
)
I receive the following error:

Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.fn_test'.

I do not get this error if I use a regular table.
HELP!The function you are creating resides in your user database, where as the temporay table is actually created in tempdb database, where your function does not exist.

To test this out I created the function in tempdb and then went into my user database (ex. testdb) and created the temporay table using your script and it worked fine.

The problem with creating a function in tempdb is that the next time SQL Server stops and then restarts the function will be gone. The way around this is to also create the function in model database, since each time SQL Server starts up it takes a copy of model to create tempdb.|||Can you post your script please. I have tried creating the function in the master database and then use it in the temp table creation like this:

z AS (master.dbo.fn_test(x,y))

and still got the error. :confused:|||Let's assume that your user database is named foo for the purpose of this posting. When you create a function named fn_test in that database, the three part name for that function is foo.dbo.fn_test. Within foo, you can refer to it as dbo.fn_test, but in other databases on the server you'll need to use the three part name, or foo.dbo.fn_test.

Since a temp table resides in tempdb, it is in another database (not really in foo at all). Because of that in order for your computed column to work properly, I think that you'll have to use:CREATE TABLE #X
(
x INT,
y INT,
z AS (foo.dbo.fn_test(x,y))
)-PatP|||I did just that and still got the same error. Previous poster mentioned that he was able to do it but haven't posted his version of the script.

Steve

Let's assume that your user database is named foo for the purpose of this posting. When you create a function named fn_test in that database, the three part name for that function is foo.dbo.fn_test. Within foo, you can refer to it as dbo.fn_test, but in other databases on the server you'll need to use the three part name, or foo.dbo.fn_test.

Since a temp table resides in tempdb, it is in another database (not really in foo at all). Because of that in order for your computed column to work properly, I think that you'll have to use:CREATE TABLE #X
(
x INT,
y INT,
z AS (foo.dbo.fn_test(x,y))
)-PatP|||I have a database called Test, where I'm creating the temporay table.

set nocount on
go
use tempdb
go
IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name = 'fn_test')
DROP FUNCTION dbo.fn_test
GO
CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
RETURNS INT AS
BEGIN
DECLARE @.z INT
SET @.z = @.x + @.y
RETURN @.z
END
GO

use test
go
CREATE TABLE #X
(
x INT,
y INT,
z AS (dbo.fn_test(x,y))
)
go
insert #x values (5,6)
go
select * from #x
go
drop table #xsqlsql

Computed columns in temp tables

I am having a problem with using UDF as part of a temp table computed
column. Here's the sample code:
IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name = 'fn_test')
DROP FUNCTION dbo.fn_test
GO
CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
RETURNS INT AS
BEGIN
DECLARE @.z INT
SET @.z = @.x + @.y
RETURN @.z
END
GO
CREATE TABLE #X
(
x INT,
y INT,
z AS (dbo.fn_test(x,y))
)
I receive the following error:
Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.fn_test'.
I do not get this error if I use a regular table.
HELP!Hi,
As far as I know, You cannot use a UDF inside the table creation. It should
be
create table #x(i int,j int,k as i+j)
insert into #x(i,j) values(1,10)
select * from #x
output will be:-
i j k
-- -- --
1 10 11
(1 row(s) affected)
Thanks
Hari
MCDBA
"Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
news:eZChvYqWEHA.2804@.TK2MSFTNGP10.phx.gbl...
> I am having a problem with using UDF as part of a temp table computed
> column. Here's the sample code:
> IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name => 'fn_test')
> DROP FUNCTION dbo.fn_test
> GO
> CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
> RETURNS INT AS
> BEGIN
> DECLARE @.z INT
> SET @.z = @.x + @.y
> RETURN @.z
> END
> GO
> CREATE TABLE #X
> (
> x INT,
> y INT,
> z AS (dbo.fn_test(x,y))
> )
> I receive the following error:
> Server: Msg 208, Level 16, State 1, Line 2
> Invalid object name 'dbo.fn_test'.
> I do not get this error if I use a regular table.
> HELP!
>|||Looks like the UDF is being looked up in the tempdb database. If you created
the UDF in tempdb, your table creation will succeed. I am not sure if this
is the expected behavior. I'll post again, if I find out more.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
news:eZChvYqWEHA.2804@.TK2MSFTNGP10.phx.gbl...
I am having a problem with using UDF as part of a temp table computed
column. Here's the sample code:
IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name ='fn_test')
DROP FUNCTION dbo.fn_test
GO
CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
RETURNS INT AS
BEGIN
DECLARE @.z INT
SET @.z = @.x + @.y
RETURN @.z
END
GO
CREATE TABLE #X
(
x INT,
y INT,
z AS (dbo.fn_test(x,y))
)
I receive the following error:
Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.fn_test'.
I do not get this error if I use a regular table.
HELP!|||Sorry for the wrong information.
Thanks Vyas. I have never tried this option.
--
Thanks
Hari
MCDBA
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:OLTxVgqWEHA.716@.TK2MSFTNGP11.phx.gbl...
> Hi,
> As far as I know, You cannot use a UDF inside the table creation. It
should
> be
> create table #x(i int,j int,k as i+j)
> insert into #x(i,j) values(1,10)
> select * from #x
> output will be:-
> i j k
> -- -- --
> 1 10 11
> (1 row(s) affected)
>
> --
> Thanks
> Hari
> MCDBA
> "Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
> news:eZChvYqWEHA.2804@.TK2MSFTNGP10.phx.gbl...
> > I am having a problem with using UDF as part of a temp table computed
> > column. Here's the sample code:
> >
> > IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name
=> > 'fn_test')
> > DROP FUNCTION dbo.fn_test
> > GO
> >
> > CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
> > RETURNS INT AS
> > BEGIN
> > DECLARE @.z INT
> > SET @.z = @.x + @.y
> > RETURN @.z
> > END
> > GO
> >
> > CREATE TABLE #X
> > (
> > x INT,
> > y INT,
> > z AS (dbo.fn_test(x,y))
> > )
> >
> > I receive the following error:
> >
> > Server: Msg 208, Level 16, State 1, Line 2
> > Invalid object name 'dbo.fn_test'.
> >
> > I do not get this error if I use a regular table.
> > HELP!
> >
> >
>|||I have thought that was the case but no luck! I modified the code to
explicitly call the function in the database where it was created and still
got the same error. Here's modified code:
use master
GO
IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name ='fn_test')
DROP FUNCTION dbo.fn_test
GO
CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
RETURNS INT AS
BEGIN
RETURN @.x + @.y
END
GO
CREATE TABLE #X
(
x INT,
y INT,
z AS (master.dbo.fn_test(x,y))
)
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:O52L6fqWEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Looks like the UDF is being looked up in the tempdb database. If you
created
> the UDF in tempdb, your table creation will succeed. I am not sure if this
> is the expected behavior. I'll post again, if I find out more.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
> news:eZChvYqWEHA.2804@.TK2MSFTNGP10.phx.gbl...
> I am having a problem with using UDF as part of a temp table computed
> column. Here's the sample code:
> IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name => 'fn_test')
> DROP FUNCTION dbo.fn_test
> GO
> CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
> RETURNS INT AS
> BEGIN
> DECLARE @.z INT
> SET @.z = @.x + @.y
> RETURN @.z
> END
> GO
> CREATE TABLE #X
> (
> x INT,
> y INT,
> z AS (dbo.fn_test(x,y))
> )
> I receive the following error:
> Server: Msg 208, Level 16, State 1, Line 2
> Invalid object name 'dbo.fn_test'.
> I do not get this error if I use a regular table.
> HELP!
>
>|||As Vyas pointed out, the issue is that you have to create the function in
tempdb. I think he is right in suggesting that you can't use UDFs that
reside in a different database in the definition of a table.
--
Jacco Schalkwijk
SQL Server MVP
"Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
news:%23siqQFrWEHA.3512@.TK2MSFTNGP12.phx.gbl...
> I have thought that was the case but no luck! I modified the code to
> explicitly call the function in the database where it was created and
still
> got the same error. Here's modified code:
> use master
> GO
> IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name => 'fn_test')
> DROP FUNCTION dbo.fn_test
> GO
> CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
> RETURNS INT AS
> BEGIN
> RETURN @.x + @.y
> END
> GO
> CREATE TABLE #X
> (
> x INT,
> y INT,
> z AS (master.dbo.fn_test(x,y))
> )
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:O52L6fqWEHA.2544@.TK2MSFTNGP10.phx.gbl...
> > Looks like the UDF is being looked up in the tempdb database. If you
> created
> > the UDF in tempdb, your table creation will succeed. I am not sure if
this
> > is the expected behavior. I'll post again, if I find out more.
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > http://vyaskn.tripod.com/
> > Is .NET important for a database professional?
> > http://vyaskn.tripod.com/poll.htm
> >
> >
> > "Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
> > news:eZChvYqWEHA.2804@.TK2MSFTNGP10.phx.gbl...
> > I am having a problem with using UDF as part of a temp table computed
> > column. Here's the sample code:
> >
> > IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name
=> > 'fn_test')
> > DROP FUNCTION dbo.fn_test
> > GO
> >
> > CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
> > RETURNS INT AS
> > BEGIN
> > DECLARE @.z INT
> > SET @.z = @.x + @.y
> > RETURN @.z
> > END
> > GO
> >
> > CREATE TABLE #X
> > (
> > x INT,
> > y INT,
> > z AS (dbo.fn_test(x,y))
> > )
> >
> > I receive the following error:
> >
> > Server: Msg 208, Level 16, State 1, Line 2
> > Invalid object name 'dbo.fn_test'.
> >
> > I do not get this error if I use a regular table.
> > HELP!
> >
> >
> >
>|||How can I create a function in tempdb while inside a stored procedure? I
don't think I can do that.
Lets assume I can use sp_executesql to create the function inside a SP. What
will be a lifespan of it? Will it get dropped once the session is closed?
will it be accessible from other sessions? Will there be a name conflict
when two sessions will try and execute the same SP?
The more I get into it, the more questions I get. Hopefully I'll come out
with good knowledge! :)
Steve
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:uCXtqSrWEHA.2520@.TK2MSFTNGP12.phx.gbl...
> As Vyas pointed out, the issue is that you have to create the function in
> tempdb. I think he is right in suggesting that you can't use UDFs that
> reside in a different database in the definition of a table.
>|||Create a permanent function in tempdb, like
USE tempdb
GO
CREATE FUNCTION dbo.fnTest
(@.x int, @.y int)
RETURNS int
AS
BEGIN
RETURN (@.x+@.y)
END
GO
Now using another database u can call this function during temp table
creation
eg.
USE Pubs
CREATE TABLE #temp(x int,y int, z as dbo.fnTest(x,y))
INSERT INTO #temp VALUES(1,2)
INSERT INTO #temp VALUES(3,2)
SELECT * FROM #temp
Roji. P. Thomas
SQL Server Programmer
"Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
news:eLwmCwRXEHA.2940@.TK2MSFTNGP09.phx.gbl...
> How can I create a function in tempdb while inside a stored procedure? I
> don't think I can do that.
> Lets assume I can use sp_executesql to create the function inside a SP.
What
> will be a lifespan of it? Will it get dropped once the session is closed?
> will it be accessible from other sessions? Will there be a name conflict
> when two sessions will try and execute the same SP?
> The more I get into it, the more questions I get. Hopefully I'll come out
> with good knowledge! :)
> Steve
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
wrote
> in message news:uCXtqSrWEHA.2520@.TK2MSFTNGP12.phx.gbl...
> > As Vyas pointed out, the issue is that you have to create the function
in
> > tempdb. I think he is right in suggesting that you can't use UDFs that
> > reside in a different database in the definition of a table.
> >
>|||Won't that function get dropped/cleaned up and will require constant
checking for its existance?
Steve
"Roji. P. Thomas" <lazydragon@.nowhere.com> wrote in message
news:e5MzPCSXEHA.3016@.tk2msftngp13.phx.gbl...
> Create a permanent function in tempdb, like
> USE tempdb
> GO
>
> CREATE FUNCTION dbo.fnTest
> (@.x int, @.y int)
> RETURNS int
> AS
> BEGIN
> RETURN (@.x+@.y)
> END
> GO
> Now using another database u can call this function during temp table
> creation
> eg.
>
> USE Pubs
>
> CREATE TABLE #temp(x int,y int, z as dbo.fnTest(x,y))
>
> INSERT INTO #temp VALUES(1,2)
> INSERT INTO #temp VALUES(3,2)
> SELECT * FROM #temp
>
> --
> Roji. P. Thomas
> SQL Server Programmer
> "Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
> news:eLwmCwRXEHA.2940@.TK2MSFTNGP09.phx.gbl...
> > How can I create a function in tempdb while inside a stored procedure? I
> > don't think I can do that.
> >
> > Lets assume I can use sp_executesql to create the function inside a SP.
> What
> > will be a lifespan of it? Will it get dropped once the session is
closed?
> > will it be accessible from other sessions? Will there be a name conflict
> > when two sessions will try and execute the same SP?
> >
> > The more I get into it, the more questions I get. Hopefully I'll come
out
> > with good knowledge! :)
> >
> > Steve
> >
> > "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote
> > in message news:uCXtqSrWEHA.2520@.TK2MSFTNGP12.phx.gbl...
> > > As Vyas pointed out, the issue is that you have to create the function
> in
> > > tempdb. I think he is right in suggesting that you can't use UDFs that
> > > reside in a different database in the definition of a table.
> > >
> >
> >
>|||You can either add the function to the model database (on the basis of which
all databases are created) or you can create a start up stored procedure to
create the function in tempdb whenever SQL Server is started. The function
will not just disappear from tempdb once you have created it, only when SQL
Server is restarted, because tempdb gets recreated then.
--
Jacco Schalkwijk
SQL Server MVP
"Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
news:ufdhcNTXEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Won't that function get dropped/cleaned up and will require constant
> checking for its existance?
> Steve
> "Roji. P. Thomas" <lazydragon@.nowhere.com> wrote in message
> news:e5MzPCSXEHA.3016@.tk2msftngp13.phx.gbl...
> > Create a permanent function in tempdb, like
> >
> > USE tempdb
> > GO
> >
> >
> > CREATE FUNCTION dbo.fnTest
> > (@.x int, @.y int)
> > RETURNS int
> > AS
> > BEGIN
> > RETURN (@.x+@.y)
> > END
> > GO
> >
> > Now using another database u can call this function during temp table
> > creation
> >
> > eg.
> >
> >
> > USE Pubs
> >
> >
> > CREATE TABLE #temp(x int,y int, z as dbo.fnTest(x,y))
> >
> >
> > INSERT INTO #temp VALUES(1,2)
> > INSERT INTO #temp VALUES(3,2)
> >
> > SELECT * FROM #temp
> >
> >
> >
> > --
> > Roji. P. Thomas
> > SQL Server Programmer
> > "Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
> > news:eLwmCwRXEHA.2940@.TK2MSFTNGP09.phx.gbl...
> > > How can I create a function in tempdb while inside a stored procedure?
I
> > > don't think I can do that.
> > >
> > > Lets assume I can use sp_executesql to create the function inside a
SP.
> > What
> > > will be a lifespan of it? Will it get dropped once the session is
> closed?
> > > will it be accessible from other sessions? Will there be a name
conflict
> > > when two sessions will try and execute the same SP?
> > >
> > > The more I get into it, the more questions I get. Hopefully I'll come
> out
> > > with good knowledge! :)
> > >
> > > Steve
> > >
> > > "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> > wrote
> > > in message news:uCXtqSrWEHA.2520@.TK2MSFTNGP12.phx.gbl...
> > > > As Vyas pointed out, the issue is that you have to create the
function
> > in
> > > > tempdb. I think he is right in suggesting that you can't use UDFs
that
> > > > reside in a different database in the definition of a table.
> > > >
> > >
> > >
> >
> >
>

complicated select

through no fault of my own, there exists a table with varchar(50)
column, the values of which are comma separated integers. something
like so:
create table foo (
fookey int primary key not null,
foointegerlist1 varchar(50) null,
foointegerlist2 varchar(50) null,
fooprice money null,
foosize int null
)
insert into foo values (1, ',2,3', '1', 10, 100)
insert into foo values (2, '3', '4', null, 100)
insert into foo values (3, ',1,11', ',1,5,6', 10, 100)
insert into foo values (4, ',3,5', ',11', 10, null)
(yes, the leading comma will randomly appear)
unpleasant? yes. scheduled to be redesigned? yes. but for now, this is
what i have to contend with.
the problem i'm having is that there is another table with a very
similar column, like so:
create table foomatch (
foomatchkey int primary key not null,
foomatchintegerlist1 varchar(50) null
foomatchintegerlist2 varchar(50) null,
foomatchprice money null,
foomatchsize int null
)
insert into foomatch values (1, ',2', ',4,5,6', 10, 100)
insert into foomatch values (2, ',1,3,6', ',1', null, null)
the goal of the select statement is to capture all of the rows in the
table foo that "match" the values of a given single row in the foomatch
table. the rules for matching are as follows:
foomatch.foomatchprice = foo.fooprice AND
foomatch.foomatchsize = foo.foosize AND
(this is where it gets dicey for me)
at least ONE of the integer values in foomatch.foomatchintegerlist1
must occur in the foo.foointegerlist1 column AND
at least ONE of the integer values in foomatch.foomatchintegerlist2
must occur in the foo.foointegerlist1 column
where foomatch.foomatchkey = 1 (for example)
i have a user defined function that can turn a comma separated string
value into a single column table. i mention this in case building
tables of the values will make this easier / possible (perhaps with
relational math?)
thanks in advance for any help, and just let me know if you need
clarification,
jasonhi jason,
its quite a long story
use string manipulations to meet the desired solution.
we have several string functions that you can use
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"jason" wrote:

> through no fault of my own, there exists a table with varchar(50)
> column, the values of which are comma separated integers. something
> like so:
> create table foo (
> fookey int primary key not null,
> foointegerlist1 varchar(50) null,
> foointegerlist2 varchar(50) null,
> fooprice money null,
> foosize int null
> )
> insert into foo values (1, ',2,3', '1', 10, 100)
> insert into foo values (2, '3', '4', null, 100)
> insert into foo values (3, ',1,11', ',1,5,6', 10, 100)
> insert into foo values (4, ',3,5', ',11', 10, null)
> (yes, the leading comma will randomly appear)
> unpleasant? yes. scheduled to be redesigned? yes. but for now, this is
> what i have to contend with.
> the problem i'm having is that there is another table with a very
> similar column, like so:
> create table foomatch (
> foomatchkey int primary key not null,
> foomatchintegerlist1 varchar(50) null
> foomatchintegerlist2 varchar(50) null,
> foomatchprice money null,
> foomatchsize int null
> )
> insert into foomatch values (1, ',2', ',4,5,6', 10, 100)
> insert into foomatch values (2, ',1,3,6', ',1', null, null)
> the goal of the select statement is to capture all of the rows in the
> table foo that "match" the values of a given single row in the foomatch
> table. the rules for matching are as follows:
> foomatch.foomatchprice = foo.fooprice AND
> foomatch.foomatchsize = foo.foosize AND
> (this is where it gets dicey for me)
> at least ONE of the integer values in foomatch.foomatchintegerlist1
> must occur in the foo.foointegerlist1 column AND
> at least ONE of the integer values in foomatch.foomatchintegerlist2
> must occur in the foo.foointegerlist1 column
> where foomatch.foomatchkey = 1 (for example)
> i have a user defined function that can turn a comma separated string
> value into a single column table. i mention this in case building
> tables of the values will make this easier / possible (perhaps with
> relational math?)
> thanks in advance for any help, and just let me know if you need
> clarification,
> jason
>|||thanks for the reply Jose.
i spent some time looking at string manipulation approaches, i'm not
sure string manipulation will do the trick here. even if we boiled it
down to a single comparison, how would you use string manipulation to
answer the question are any of the comma separated elements in ',1,2,3'
also in the comma separated list ',3,4,5'? i've spent some time going
down this path, and it didn't yield anything close to sane results.
though if you can post something a little more specific, i'd be happy
to entertain the notion.
and yes, my explanations are rarely brief. this isn't even close to one
of my longer ones :)|||If you use tables or table valued user defined functions for the integer
lists it is reasonably straightforward:
Assuming there are tables/udfs foolist1 (fookey, foointeger1) ,
foomatchlist1 (foomatchkey, foomatchinteger1) and foomatchlist2
(foomatchkey, foomatchinteger2)
SELECT f.<column list>
FROM foo f
INNER JOIN foomatch fm
ON fm.foomatchprice = f.fooprice
AND fm.foomatchsize = f.foosize
WHERE EXISTS
(
SELECT NULL FROM foolist1 fl1
INNER JOIN foomatchlist1 fml1
ON fl1.foointeger1 = fml1.foomatchinteger1
WHERE fl1.fookey = f.fookey AND fml1.foomatchkey = fm.foomatchkey
)
AND EXISTS
(
SELECT NULL FROM foolist1 fl1
INNER JOIN foomatchlist2 fml2
ON fl1.foointeger1 = fml2.foomatchinteger2
WHERE fl1.fookey = f.fookey AND fml2.foomatchkey = fm.foomatchkey
)
Jacco Schalkwijk
SQL Server MVP
"jason" <iaesun@.yahoo.com> wrote in message
news:1128432811.427135.37460@.g44g2000cwa.googlegroups.com...
> through no fault of my own, there exists a table with varchar(50)
> column, the values of which are comma separated integers. something
> like so:
> create table foo (
> fookey int primary key not null,
> foointegerlist1 varchar(50) null,
> foointegerlist2 varchar(50) null,
> fooprice money null,
> foosize int null
> )
> insert into foo values (1, ',2,3', '1', 10, 100)
> insert into foo values (2, '3', '4', null, 100)
> insert into foo values (3, ',1,11', ',1,5,6', 10, 100)
> insert into foo values (4, ',3,5', ',11', 10, null)
> (yes, the leading comma will randomly appear)
> unpleasant? yes. scheduled to be redesigned? yes. but for now, this is
> what i have to contend with.
> the problem i'm having is that there is another table with a very
> similar column, like so:
> create table foomatch (
> foomatchkey int primary key not null,
> foomatchintegerlist1 varchar(50) null
> foomatchintegerlist2 varchar(50) null,
> foomatchprice money null,
> foomatchsize int null
> )
> insert into foomatch values (1, ',2', ',4,5,6', 10, 100)
> insert into foomatch values (2, ',1,3,6', ',1', null, null)
> the goal of the select statement is to capture all of the rows in the
> table foo that "match" the values of a given single row in the foomatch
> table. the rules for matching are as follows:
> foomatch.foomatchprice = foo.fooprice AND
> foomatch.foomatchsize = foo.foosize AND
> (this is where it gets dicey for me)
> at least ONE of the integer values in foomatch.foomatchintegerlist1
> must occur in the foo.foointegerlist1 column AND
> at least ONE of the integer values in foomatch.foomatchintegerlist2
> must occur in the foo.foointegerlist1 column
> where foomatch.foomatchkey = 1 (for example)
> i have a user defined function that can turn a comma separated string
> value into a single column table. i mention this in case building
> tables of the values will make this easier / possible (perhaps with
> relational math?)
> thanks in advance for any help, and just let me know if you need
> clarification,
> jason
>|||ahh! i think i see the solution here. yes, i think i can modify my
udf's slightly to make this work. i will give that a shot.
thanks very much
jason