function in select statement - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

function in select statement

Author

Topic

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2008-10-13 : 21:33:41

hi ,i've a function that returns a table (with two fields). I want to use this function as a field in another sql statement something like below
select claim.*,
(SELECT [refdate] FROM dbo.myfunction(claim.[claimID])) AS [Referring Date],
(SELECT [Referfrom] FROM dbo.myfunction(claim.[claimID])) AS [Referring Practitioner ],
from claim inner join person on claim.id=person.claim

as you can see above am calling function twice for the same id is there anyway I can reduce It to one call ? I tried following but getting "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS." error

select claim.*,
(SELECT * FROM dbo.myfunction(claim.[claimID]))
from claim inner join person on claim.id=person.claim

Cheers

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-14 : 00:28:56

[code]select claim.*,
g.refdate AS [Referring Date],
g.Referfrom AS [Referring Practitioner]
from claim
cross apply dbo.myfunction(claim.claimID) as g
where exists (select * from person where person.claim = claim.id)[/code]

E 12°55'05.63"
N 56°04'39.26"

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2008-10-14 : 16:12:53

excellent, exactly what am looking for. thank you very much for that Peso. by the way I changed cross apply to outer apply as I wanted non matching rows from claim table also.

Cheers

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2008-10-14 : 16:14:51

by the way I did not add where condition like you did in your post and it's returns all rows matter. just wondering is it needed ? my query is
select claim.*,
g.refdate AS [Referring Date],
g.Referfrom AS [Referring Practitioner]
from claim
outer apply dbo.myfunction(claim.claimID) as g

Cheers

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2008-10-14 : 16:15:50

sorry ignore my last post. I understood now.thanks

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources