CODEWITHSUNDEEP
sqlsql-server
Mavershang
Mavershang

Reputation: 1278

Microsoft SQL query help

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 work, but when I call function dbo.fn_SeqAvgSimilarityUnderNode, error pops out.

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?

Upvotes: 1

Views: 57

Answers (1)

John Petrak
John Petrak

Reputation: 2928

Without seeing the code for dbo.fn_SelectAlignedSequences I'm pretty sure you need to specify a column that you want to select.

Change this

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

to something like this

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

Upvotes: 2

Related Questions