Join to table returned by UDF

Question

Post reply

Join to table returned by UDF

MattieNH

SSCertifiable

Points: 7463
More actions
December 2, 2005 at 10:38 am

#92669

I need to return a recordset that has the following fields

DepositNumber
DepositDate
DepositAmount
TransferNumber
TransferAmount

The first four are easy; it's a simple join between the deposit and transfer tables. The problem is the TransferAmount. The TransferAmount is actually the sum of some amounts that are calculated in another process. The good news is that I have a UDF that returns those amounts in a table variable. The problem is that in order to run the UDF, I need to pass the TransferNumber to it, and I don't know how how to structure the join.

I'm not surprised this hasn't worked, but conceptually it's what I think I want.

select   d.DepositID,
            d.DepositDate,
            d.DepositAmount,
            IsNull(t.TransferID, 0)                 as TransferID,
            sum(a.TransferAmount)
From Deposit d
            Left Outer Join Transfer t
                        on d.TransferID = t.TransferID
            Inner Join
                        (SELECT RevenueTransferID,
                                    TransferAmount)
                        FROM dbo.fnTransferEntries(t.TransferID)     as a
                        on d.TransferID = a.TransferID
GROUP BY       d.DepositID,
                        d.DepositDate,
                        d.DepositAmount,
                        IsNull(t.TransferID, 0)

The text in blue is where I try to join the table created by the UDF to the database tables, and the text in red is what it doesn't like.

Thanks for any help you can offer..

Mattie

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 1

INNER query will return multiple tables and is not possible to do such a join

CREATE THE UDF AS dbo.fnTransferEntries() such that it returns all transferId, and TransferAmount

without a parameter passed to it.

then

SELECT d.DepositID,

d.DepositDate,

d.DepositAmount,

IsNull(t.TransferID, 0) as TransferID,

t.TransferAmount

From Deposit d

LEFT OUTER JOIN

(

SELECT TransferID,

SUM(TransferAmount) TransferAmount

FROM

dbo.fnTransferEntries()

GROUP BY RevenueTransferID) t

ON

d.TransferID = D.TransferID

Regards,
gova

Sergiy SSC Guru Points: 110208 More actions · Answer 2

1st. fnTransferEntries must be a view.

2nd. This view must contain column TransferId and the statement "GROUP BY TransferId".

3rd. Use

......

INNER JOIN dbo.TransferEntries te on te.TransferId = T.TransferId

4th. Consider indexing of this view.

_____________
Code for TallyGenerator

MattieNH SSCertifiable Points: 7463 More actions · Answer 3

Thank you both for your responses.

The reason I wrote the UDF with the parameter was to limit the amount of data that needed to be included when I did the JOIN. Obviously, if it isn't useful, I'll drop the parameter, but is that something I should ever be concerned about?

I'm reluctant to put the GROUP BY in the view, because although in this instance I only care about the total, I can certainly foresee circumstances where I would want the row level detail. I want to make this UDF/view as flexible as possible, because there's a hellacious amount of logic required to end up with the data required.

I'd like to rewrite the function as a view without the Group By, and then use it in my stored procedure somehow. Does that sound like the best combination of flexibility and performance?

Mattie