Join with function - 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 2000 Forums

Transact-SQL (2000)

Join with function

Author

Topic

cornall
Posting Yak Master

148 Posts

Posted - 2008-07-09 : 05:08:00

Hi

I have a select query as follows


Select ID FROM table1

I have a function which takes the input ID and year and returns some vlaues


MyFunction(1234,2008)

This returns a table


division  cost
  A       2000
  B       2456
  C       1589

What I want to do is join the results of


SELECT sum(cost) FROM MyFunction(1234,2008)


Select ID FROM table1

Where each id in the select replaces 1234 in the function

So something like



Select ID,SUM(cost) FROM table1,MyFunction(ID,2008)

This doesn't work. Is there any way to do this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-09 : 05:35:50

SELECT ID, SUM(Cost) FROM Table1
GROUP BY ID

E 12°55'05.25"
N 56°04'39.16"

cornall
Posting Yak Master

148 Posts

Posted - 2008-07-09 : 05:37:02

But cost isn't part of table 1. Cost comes from the results of the function.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-09 : 06:20:03

And the function returns a scalar value? Or a resultset?

E 12°55'05.25"
N 56°04'39.16"

cornall
Posting Yak Master

148 Posts

Posted - 2008-07-09 : 06:24:52

A result set as per my original post

quote:

This returns a table


division  cost
  A       2000
  B       2456
  C       1589

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-09 : 06:56:31

This can be done by using APPLY operator if you're using SQL 2005. I think if you want to do this in 2000 you might need to modify function to return a scalar value which will be SUM(Cost) upon passing the ID to it so that you can just call it in select itlesf

SELECT MyFunction(ID,2008) FROM Table

cornall
Posting Yak Master

148 Posts

Posted - 2008-07-09 : 07:02:03

Cheers can also do it using a cursor but that takes 19 mins to execute LOL me and my cursors!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-09 : 12:17:48

Show us the code for function.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources