Help --> How to join a function to a table

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)

Help --> How to join a function to a table

Author

Topic

varunragul
Starting Member

10 Posts

Posted - 2007-12-05 : 02:22:16

i need to join a function to a table , the functions returns as a table value. the parameters passed itself a column of the joined table , i tried i am getting syntax error , could any body help on this , this is urgent.

sample query that throws error
select f.code , e.Date from (Select * from dbo.fn_Date ('27 oct 2005',
f.Settle_Days,
3,
f.AssetID,
4,
2,
4) e

join dox b on 1=1
JOIN rog f
ON f.Code = b.Code
WHERE b.End_Date = '27 oct 2005 12:00:00'
AND f.Trust = 'TMC'

any help is highly appreciated.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2007-12-05 : 02:28:57

a function that returns a table gets treated exactly like a table when using the join syntax.
select a.col1, b.col1
from tablea a
inner join dbo.myfunction(param1, param2) b
on a.id1 = b.id1

I'm sure if you give it some thought - you can apply it to your existing problem

Duane.

varunragul
Starting Member

10 Posts

Posted - 2007-12-05 : 02:34:45

my problem is that , the parameter to that function itself is acolumn of the table to be joined ,

select a.col1, b.col1
from tablea a
inner join dbo.myfunction(a.id2,a.id3) b
on a.id1 = b.id1

please help on this

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-05 : 02:48:42

You need to make use of CROSS APPLY to make it work.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 03:51:46

You didn't even post the complete code

select	f.code,
	e.Date
from	(
		Select	*
		from	dbo.fn_Date('27 oct 2005', f.Settle_Days, 3, f.AssetID, 4, 2, 4) e
		join	dox As b on 1 = 1 
		JOIN	rog as f ON f.Code = b.Code 
		WHERE	b.End_Date = '27 oct 2005 12:00:00'
			AND f.Trust = 'TMC'

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

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-05 : 03:58:29

Peter,

Can you refer table columns inside function this way without using CROSS APPLY?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 04:12:08

No.

I was just prettyfying the code to see what was erally going on.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 04:12:46

For example, this line

join dox As b on 1 = 1

equals to

CROSS JOIN dox AS b

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-05 : 12:00:58

i think you need something like this:-

select t.code , t.Date from
(

select f.code,e.date
from dox b
join rog f
on f.Code = b.Code
cross apply dbo.fn_Date ('27 oct 2005',f.Settle_Days,3,f.AssetID,
4,2,4)e
Where b.End_Date = '27 oct 2005 12:00:00'
AND f.Trust = 'TMC'

)t

varunragul
Starting Member

10 Posts

Posted - 2007-12-06 : 04:33:28

It dont work , i get the same error

Server: Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'f'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-06 : 04:38:13

Are you sure you are using SQL Server 2005?
Which edition?
Have you set COMPATIBILITY LEVEL to 90?

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

varunragul
Starting Member

10 Posts

Posted - 2007-12-06 : 05:12:35