ansaurus

Question

How to make left outer join a UD-fuction with any sql table?

Answer 1

+2 A:

Edit

If you do not need to pass a parameter to your UDF, you can simply join the table with your UDF. If you need to pass parameters, use the CROSS APPLY as mentioned in the other answer.

SELECT *
FROM dbo.YourTable yt
     LEFT OUTER JOIN dbo.UDF_YourUDF() yd ON yd.YourTableID = yt.YourTableID

Lieven 2009-05-11 07:00:00

<pre><code>select Count(Page) as VisitingCount,[Time] from ( SELECT Page,Date,[user], dbo.fn_GetActivityLogArranger2(Date,'halfhour') as [Time] FROM scr_SecuristLog sl left outer join dbo.DefaultActivity2() da on da.[Date]=sl.[Date] ) scr_SecuristLog where Date between '2009-05-02' and '2009-05-03' group by [Time] order by [Time] asc </code></pre><h3>Error:</h3>Msg 209, Level 16, State 1, Line 5Ambiguous column name 'Date'.Msg 209, Level 16, State 1, Line 6Ambiguous column name 'Date'.

Phsika 2009-05-11 07:23:05

-1 This just works for functions without any parameters.

2009-05-11 07:23:25

select Count(Page) as VisitingCount,[Time] from ( SELECT Page,Date,[user], dbo.fn_GetActivityLogArranger2(Date,'halfhour') as [Time] FROM scr_SecuristLog sl left outer join dbo.DefaultActivity2() da on da.[Date]=sl.[Date] ) scr_SecuristLog where Date between '2009-05-02' and '2009-05-03' group by [Time] order by [Time] asc

Phsika 2009-05-11 07:23:31

But This query give me error:Msg 209, Level 16, State 1, Line 5Ambiguous column name 'Date'.Msg 209, Level 16, State 1, Line 6Ambiguous column name 'Date'.

Phsika 2009-05-11 07:24:04

@Moheb, I know. I have added that requirement as a comment to the other answer 7 minutes before you wrote your comment (and downvoted this answer).

Lieven 2009-05-11 07:34:49

@ykaratoprak. If you need to pass a parameter from your table, you need the CROSS APPLY.

Lieven 2009-05-11 07:36:39

Answer 2

+4 A:

Use SQL Server 2005's new APPLY clause. The APPLY clause let's you join a table to a table-valued-function. That let's you write a query like this:

SELECT  C.CustomerID, 
    O.SalesOrderID,
    O.TotalDue
FROM 
    AdventureWorks.Sales.Customer AS C
CROSS APPLY
    AdventureWorks.dbo.fn_GetTopOrders(C.CustomerID, 3) AS O
ORDER BY 
    CustomerID ASC, TotalDue DESC

The APPLY clause acts like a JOIN without the ON clause comes in two flavors: CROSS and OUTER. The OUTER APPLY clause returns all the rows on the left side (Customers) whether they return any rows in the table-valued-function or not. The columns that the table-valued-function returns are null if no rows are returned. The CROSS APPLY only returns rows from the left side (Customers) if the table-valued-function returns rows.

Source: Using CROSS APPLY in SQL Server 2005

EDIT: As Lieven mentioned, The CROSS APPLY is only necessary when you need to pass a value from your table to the UDF.

M. Jahedbozorgan 2009-05-11 07:09:02

The CROSS APPLY is only necessary when you need to pass a value from your table (CustomerID in your case) to the UDF. A UDF without parameters can be joined like any other table.

Lieven 2009-05-11 07:16:59

You are right. I have edited my answer to reflect that. Thank you.

M. Jahedbozorgan 2009-05-11 07:30:57

ansaurus

tags:

views:

answers:

How to make left outer join a UD-fuction with any sql table?

related questions