user420667 user420667 - 5 months ago 9
SQL Question

Is it possible to use both a filter and a view functions in the same query?

My tables are a bit like this (please excuse the inexact definitions):

CREATE TABLE JunctionTable( int Concrete1ID, int Concrete2ID, int someProperty)
CREATE TABLE Concrete1 ( int ID, int Prop1, int Prop2)
CREATE TABLE Concrete2 ( int ID, int Prop1, int Prop2, int Prop3, int Prop4)


There are foreign keys to each of the concrete tables.

And I have some functions. One function -
FilterJunction
- to get things in the
JunctionTable
that meet certain conditions, and another function -
SomeViewofConcrete2
- to return properties of a
Concrete2
row given a
Concrete2ID
.

CREATE FUNCTION [dbo].FilterJunction() Returns Table AS
BEGIN RETURN (SELECT * FROM JunctionTable WHERE someProperty < 5)
END

CREATE FUNCTION [dbo].SomeViewOfConcrete2(@ID int) RETURNS TABLE
BEGIN
Return (SELECT Prop2, Prop4 FROM Concrete2 WHERE ID = @ID)
END


Note that
SomeViewOfConcrete2
is expected to return just one row at most (and in this case, it should definitely return one row, as I've setup the appropriate foreign keys.)

So finally I'd like to run the following query:

SELECT ConcreteID1, ConcreteID2, [dbo].SomeViewOfConcrete2(Concrete2ID)
FROM [dbo].FilterJunction()


But get the following error:


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


Though if I try to run
SomeViewOfConcrete2
separately with a specific
ConcreteID2
, that query runs perfectly fine. (e.g.
SELECT * FROM [dbo].SomeViewOfConcrete2(1)


I think the problem is that I have no way to tell SQL that the function returns exactly one row, so it doesn't know how to give the expected result set of
ConcreteID1, ConcreteID2, Prop2, Prop4
, but that error message sure doesn't give a good indication of that.

One way I could solve this problem is to just do:

SELECT ConcreteID1, ConcreteID2, Prop2, Prop4
FROM [dbo].FilterJunction() F
JOIN CONCRETE2 C
ON F.ConcreteID2 = C.ID


But that doesn't use the
SomeViewOfConcrete2
function, and thus doesn't encourage code reuse.

So I apologize but my question is a bit two fold:


  1. Is it possible to design the query in a way that encourages code reuse of both the filter and the viewing function?

  2. Why is SQL throwing the error it gives and not saying something else?



Thanks.

Answer

This is because a table valued function is designed to return any number of rows. You can't just shove a table like this as a column. You can however use APPLY here quite effectively.

SELECT ConcreteID1
    , ConcreteID2
    , svc.Prop2 --or whatever columns from that function
    , svc.Prop4
FROM [dbo].FilterJunction() fj
cross apply [dbo].SomeViewOfConcrete2(fj.Concrete2ID) svc