Function call with field from a table as parameter

Question

Function call with field from a table as parameter

Jean-Pierre PONS

SSC-Addicted

Points: 497
More actions
May 17, 2006 at 12:25 am

#112240

Hi,
I want to call a table function for each row in a select using a record field as parameter for the function. And that's the problem, the syntax alias.field in the function call generates an error :
Serveur : Msg 170, Niveau 15, État 1, Ligne 3
Ligne 3 : syntaxe incorrecte vers '.'.
Here's the way to reproduce the above :
use NorthWind
go
create Function UF_TABLETEST (@catID int)
returns TABLE
as
return (select count(*) as NbTot, sum(UnitsInStock) as NbUinS, sum(UnitsOnOrder) as NbUinO
from Products where CategoryID = @catID)
go
select c.CategoryName, st.NbTot, st.NbUinS, st.NbUinO
from Categories c, dbo.UF_TABLETEST(c.CategoryID) st
where c.CategoryID > 0
Did anyone ever found such a pb and the way to get rid of it ?
Thanks !

Viewing 7 posts - 1 through 6 (of 6 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What you're trying to do can't be done in SQL 2000 (function in FROM with parameter been a field from the query). It's a new feature in SQL 2005 (CROSS APPLY)

What are you trying to do? Maybe someone can suggest another method. I can see easy alternatives for the sample code you posted, but I don't know if that's a simplification.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jean-Pierre PONS SSC-Addicted Points: 497 More actions · Answer 2

Hi GilaMonster

In fact, you're right, this is a simplification just here to generate the error for convenience.

But in the real world, I need to search for a single occurrence in a historic table using a DATE field coming from another table and the point is that I can find more than one record matching the criterias. Then I must use the fields from this historic table to likn to other tables with value also comming from the preceding ones...

I've planned to write a function giving the TOP 1 of this subset, and calling it in the main select, but it was giving me the error above.

Where I need a single field from the historic table I can use max() and group by..., but when I need several ones, this can't be done.

Thks

win SSCommitted Points: 1569 More actions · Answer 3

I ran into the same problem this week trying to expose 2 values from a table function for the current row. I ended up converting the table function to 2 standard functions and returning each value with a separate function call in the select list.

Does anybody know whether the cached result set produced by the select statement shared by both functions is used in both cases, or does the second function call create a new result set.

Win

Sergiy SSC Guru Points: 110206 More actions · Answer 4

You better create a view

CREATE VIEW CatSummary

AS

select CategoryID, count(*) as NbTot, sum(UnitsInStock) as NbUinS, sum(UnitsOnOrder) as NbUinO

from Products

GROUP BY CategoryID

and join this view to you statement:

select c.CategoryName, st.NbTot, st.NbUinS, st.NbUinO

from Categories c

INNER JOIN CatSummary st ON C.CategoryID = st.CategoryID

where c.CategoryID > 0

_____________
Code for TallyGenerator

Jean-Pierre PONS SSC-Addicted Points: 497 More actions · Answer 5

Hi Sergiy.

Of course you 're right ! In the example i'd better create a view, but in the real world, my problem is that I want to search for a single occurrence in a table for a partial key value and a given date. I'd better illustrate the example :

Historic table : keysegment1, fromdate, todate, keysegment2, other fields...

I want to retrieve some "others fields" from this table given the keysegment1 and a date which falls between fromdate et todate, but without keysegment2. In fact I can only retrieve 2 or 3 records for the given date, but I just want to get the first of them (the only others fields i'm interrested in are the same for the 2 or 3 occurrences...). And of course, this is needed in a more complex select statement..

I could realize this in a stored proc (and in fact i did it for other purposes), but in a complex select, this seems really to be the scope of a function ?...

stax68 SSChampion Points: 11711 More actions · Answer 6

select h.*
from historic_table h
join (
select keysegment1, min(keysegment2) keysegment2
from Historic_table h
where keysegment1 = @KS1
--ignore time-portion for simplicity
and fromdate <= @date
and todate >= @date
group by keysegment1
) V
on h.keysegment1 = V.keysegment1
and h.keysegment2 = V.keysegment2

would do it. In fact given that the columns you want are fully determined by keysegment1 and the dates, you could just use:

select keysegment1
,min(keysegment2) keysegment2
,min(otherfield1) otherfield1
,min(otherfield2) otherfield2
from Historic_table h
where keysegment1 = @KS1
--ignore time-portion for simplicity
and fromdate <= @date
and todate >= @date
group by keysegment1

Performance may be a consideration of course, and there are other ways....

Tim

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant