Calling function in another function

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

General SQL Server Forums

New to SQL Server Programming

Calling function in another function

Author

Topic

mavershang
Posting Yak Master

111 Posts

Posted - 2011-07-18 : 22:21:50

Hi there.

I am trying to call a table-valued function (dbo.fn_SelectAlignedSequences) from another scalar-valued function (fn_SeqAvgSimilarityUnderNode2). Here is the query


Create Function [dbo].[fn_SeqAvgSimilarityUnderNode]
	(@AlnID int, @ParentTaxID int, @SeqTypeID int, @LocationID int) 
RETURNS FLOAT
AS
BEGIN

	Declare @AvgSim float

	;With Seqs (SeqID) as
	(-- All aligned sequences under the node
		select dbo.fn_SelectAlignedSequences(@AlnID, @ParentTaxID, @SeqTypeID, @LocationID)
	)
	
	Select  @AvgSim = AVG(Similarity)
	From	(
				Select	 s1.SeqID
						,s2.SeqID
						,dbo.fn_pairwiseSimilarity(@AlnID, s1.SeqID, s2.SeqID) as 'Similarity'
				From	Seqs s1 cross join Seqs s2
				Where	s1.SeqID < s2.SeqID
			) t

	return @AvgSim

END

The query does not work.

Error message"Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fn_SelectAlignedSequences", or the name is ambiguous.

Is there anything I did wrong?

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-18 : 22:36:41

if fn_SelectAlignedSequences is a table value function, you should be treating it like a table


	;With Seqs (SeqID) as
	(-- All aligned sequences under the node
		select *
                from   dbo.fn_SelectAlignedSequences(@AlnID, @ParentTaxID, @SeqTypeID, @LocationID)
	)

KH
[spoiler]Time is always against us[/spoiler]

mavershang
Posting Yak Master

111 Posts

Posted - 2011-07-18 : 22:41:33

Great. You save my hours. Thanks.

quote:
Originally posted by khtan

if fn_SelectAlignedSequences is a table value function, you should be treating it like a table

;With Seqs (SeqID) as
(-- All aligned sequences under the node
select *
from dbo.fn_SelectAlignedSequences(@AlnID, @ParentTaxID, @SeqTypeID, @LocationID)
)

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources