creating a table from a user defined fucntion call

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

creating a table from a user defined fucntion call

Author

Topic

heze
Posting Yak Master

192 Posts

Posted - 2006-09-05 : 17:52:14

hi I hant to do the following:
--I define my funciton:
create function myUDFunction......
the fucntion returns an inline table
-------
--I then call the fucntion
select *
into #myTable
from myUDFunction(@par)
--
--I cant, the error message is the following:
Server: Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'Tbl1004' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'Tbl1004' does not match with a table name or alias name used in the query.
--------------

I have tried with table variables and predefined concrete tables as well with similar results, is it possible to create another table from the resultset provided by the function call?

Thank you

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-09-05 : 18:43:14

It sounds like you have an error in the function code, can you post it?

Alternately, look in the UDF code for "Tbl1004", that should point you to where the error is.

heze
Posting Yak Master

192 Posts

Posted - 2006-09-06 : 01:12:33

Hi Rob I went back yes indeed there was a problem but not of sintax. The table that my udfunction is returning uses a subquery
like:
return(
select * from myTable mt
where year=(
select max(year) from mytable mti where mt.Id=mti.Id
)
)
-------
If I take the "year=(
select max(year) from mytable mti where mt.Id=mti.Id
)"
away, the fucntion call works fine
select * into #temp from myFunction()
----
is there a way out of here?

Thanks

By the way, Did you post the pivot table creator?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-06 : 01:27:36

You still don't say what is wrong with the query, but I would suspect that it is the
mt.id = mti.id

that is wrong. If id is primary column, that would return all rows anyway.

If you wnat us to help you, post the MyTable DDL together with some sample data and the expected output based on that sample data.

Peter Larsson
Helsingborg, Sweden

heze
Posting Yak Master

192 Posts

Posted - 2006-09-06 : 01:56:14

seems this problem has nothing to do with the udf but with building an inline table from a query that uses a subquery, the following links show a similar problem but havent found a solution to my problem
http://www.dbforums.com/showthread.php?t=916080
http://www.opensubscriber.com/message/arslist@googlegroups.com/1917610.html

heze
Posting Yak Master

192 Posts

Posted - 2006-09-06 : 02:02:12

hi Peso:

this is my trouble:
select *
into [MyDatabase].dbo.COL_scores_MaxDates
from myDatabase.dbo.scores ts
where TestDate =(
select max(TestDate) as maxTestDate
from myDatabase.dbo.scores tsi
where tsi.ID=ts.ID and tsi.TestCode=ts.TestCode
)

if I eliminate the subquery, the table is created, if I leave it, it gives me the same kind of problem I reported earlier

thansk

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-06 : 02:15:17

If you drop the "INTO ..." line, and keep the WHERE clause with subquery, what does the query return then?

Peter Larsson
Helsingborg, Sweden

heze
Posting Yak Master

192 Posts

Posted - 2006-09-06 : 10:20:44

Peso, the query runs well if I eliminate the INTO.. line or the subquery, when I include both is when I am having the problem

heze
Posting Yak Master

192 Posts

Posted - 2006-09-06 : 10:29:35

peso, in your previous post you suggested that the Id column could be the primary key, it is not, you can have replicated ids in the same table, one of the differences is the date in which the record was created, and that is why I need the latest record created for that id and for that code.
Going back to my previous post, if I leave the where clause but eliminate the subquery there is no problem, it is only the subquery that is not allowing me to create the "COL_scores_MaxDates" table

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-06 : 10:57:23

Your subquery will produce several MAX(Year). Actually on for each Id. Exchange "=" to "IN" and see what happens.
What is your objective with the query?

Peter Larsson
Helsingborg, Sweden

heze
Posting Yak Master

192 Posts

Posted - 2006-09-06 : 11:04:24

Peso, I already did the "in" for "=" and the problem persists,
the table keeps several records per ID but I only want one of them, answering your question, the one created in the mopst recent date for that specific id, if I dont do the max()... subquery, the script will return duplicated id's

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources