Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to join a table-valued Function


How to join a table-valued Function

Author
Message
Mike C
Mike C
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1349 Visits: 1168
Example removed at Sergiy's request.
Sergiy
Sergiy
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5811 Visits: 11389
Mike,
unfortunately there is no such thing as "CROSS APPLY" in MS SQL 2000.
Nestor Jarquin
Nestor Jarquin
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 137
Thanks a lot for your time and help....
I'll give it a try.
Mike C
Mike C
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1349 Visits: 1168
Sergiy,

Would it make you feel better if I said this feature was introduced in SQL 2005, as I did a couple of times in the first couple of posts of this entire thread?
Sergiy
Sergiy
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5811 Visits: 11389
Mike, it would be nice if you'd pay some attention to the name of the forum.
It's on top of the page, between "Home" and "T-SQL".

There is no point to repeatedly post SQL2005 solution not appropriate to SQL2000 on this forum.
Mike C
Mike C
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1349 Visits: 1168
Sergiy (10/17/2007)
Mike, it would be nice if you'd pay some attention to the name of the forum.
It's on top of the page, between "Home" and "T-SQL".

There is no point to repeatedly post SQL2005 solution not appropriate to SQL2000 on this forum.


Thank you for the constructive feedback Sergiy. I was, however, responding to questions from another poster who asked for specific examples after I mentioned that it was possible in SQL 2005 with censored. I sincerely apologize if my answering someone else's question has caused you undue stress.

To show my sincerity, and to bring this conversation to a complete and total end, I've removed all references to non-SQL 2000-specific sample code, methods, and keywords from all of my posts in this thread. I sincerely hope that relieves your anxiety.

Thanks again!
Sergiy
Sergiy
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5811 Visits: 11389
Mike C (10/17/2007)
Sergiy (10/17/2007)
Mike, it would be nice if you'd pay some attention to the name of the forum.
It's on top of the page, between "Home" and "T-SQL".

There is no point to repeatedly post SQL2005 solution not appropriate to SQL2000 on this forum.


Thank you for the constructive feedback Sergiy. I was, however, responding to questions from another poster who asked for specific examples after I mentioned that it was possible in SQL 2005 with censored. I sincerely apologize if my answering someone else's question has caused you undue stress.

To show my sincerity, and to bring this conversation to a complete and total end, I've removed all references to non-SQL 2000-specific sample code, methods, and keywords from all of my posts in this thread. I sincerely hope that relieves your anxiety.

Thanks again!

Mike,
First of all I never requested to remove anything from you posts. Don't lie.

Second, there was no any anxiety from my side, only some nervous reaction from yours.
I'm not a doctor, don't know what to suggest to relieve your anxiety.

Third, you posted same code 3 times. For what reason? Trying to prove yourself insisting on your suggestion?
Bad call, because
Forth, CROSS APPLY sucks in terms of performance, as most of the features introduced in SQL2005. Try to compare it with simple view instead of that table function and see the strong reason why CROSS APPLY should never be used.
Alec Griffiths-324451
Alec Griffiths-324451
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 23
The problem is a google search such as '"table function" "sql server" join parameter' returns this post and I am using SQL server 2005. Would be nice if there was a link from here to where the post should be..
J-440512
J-440512
SSC-Addicted
SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)

Group: General Forum Members
Points: 447 Visits: 949
Sergiy (10/17/2007)
Mike,
unfortunately there is no such thing as "CROSS APPLY" in MS SQL 2000.



In MS SQL Server 2000, you can get a cartesian product simply like this

[font="Arial"]create table TableA (pk int not null primary key identity, AVal varchar(10))

create table TableB (pk int not null primary key identity, BVal varchar(10))


insert into TableA (AVal) values ( 'A1')
insert into TableA (AVal) values ( 'A2')
insert into TableA (AVal) values ( 'A3')


insert into TableB (BVal) values ( 'B 100')
insert into TableB (BVal) values ( 'B 200')
insert into TableB (BVal) values ( 'B 300')
insert into TableB (BVal) values ( 'B 400')

SELECT A.Aval, B.BVal
FROM TableA A, TableB B

Aval BVal
A1 B 100
A2 B 100
A3 B 100
A1 B 200
A2 B 200
A3 B 200
A1 B 300
A2 B 300
A3 B 300
A1 B 400
A2 B 400
A3 B 400

(12 row(s) affected)
[/font]
Matt Miller (#4)
Matt Miller (#4)
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7624 Visits: 18043
J (4/2/2008)
Sergiy (10/17/2007)
Mike,
unfortunately there is no such thing as "CROSS APPLY" in MS SQL 2000.



In MS SQL Server 2000, you can get a cartesian product simply like this


J -

Note that he was talking about cross APPLY, not cross JOIN. CROSS APPLY is used to call table-valued functions based on column values. It is essentially a fancy implicit version of correlated sub-queries, not the Cartesian Product as done with CROSS JOIN.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search