Join a query on a UDF with parameter from select

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

SQL Server 2000 Forums

Transact-SQL (2000)

Join a query on a UDF with parameter from select

Author

Topic

gruelurks
Starting Member

1 Post

Posted - 2002-11-07 : 09:58:15

Hi all,

I have a query where I want to join to the results of a user-defined function in SQL 2000.

The function returns a single rowset consisting of 3 fields. I was hoping to be able to do it this way to avoid having to use a cursor to reiterate thru the original rowset to update the 3 fields I need from the function, but I keep getting errors. Am I going about this the wrong way?

Mick

<snip>
SELECT DISTINCT
F.FAC_NAME,
LF.LFC_ID,
LOB.LOB_ID,
LOB.LOB_NAME,
-- #######################
-- Next 3 are returned from function (LC)
-- currently using a cursor after this inital select to populate them
-- #######################
LC.LOB_ID,
LC.LOB_LFT,
LC.LOB_RGT,
LOB_TREEID
FROM
FACILITY F
INNER JOIN
Lob_Fac LF
ON F.FAC_ID = LF.FAC_ID
INNER JOIN
LineofBusiness LOB
ON LF.LOB_ID = LOB.LOB_ID
INNER JOIN
PerformanceData PD
ON LF.LFC_ID = PD.LFC_ID
INNER JOIN
-- #######################
-- This next line contains the function call,
-- and returns the error Incorrect syntax near '.'.
-- #######################
LOB_CEILING (LF.LFC_ID) LC
ON LF.LFC_ID = LC.LFC_ID
WHERE
LF.FAC_ID IN (1,2,3,4,5)
ORDER BY LF.LFC_ID
</snip>

The function returns these fields:
LOB_ID INTEGER,
LOB_LFT INTEGER,
LOB_RGT INTEGER,
LOB_NAME NVARCHAR(50),
LFC_ID INTEGER

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-11-07 : 12:19:29

Could you give the syntax for the LOB_ceiling function?

-----------------------
SQL isn't just a hobby, It's an addiction

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources