SQL Clone
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
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6573 Visits: 1172
Example removed at Sergiy's request.
Sergiy
Sergiy
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25943 Visits: 12475
Mike,
unfortunately there is no such thing as "CROSS APPLY" in MS SQL 2000.
Nestor Jarquin
Nestor Jarquin
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 143
Thanks a lot for your time and help....
I'll give it a try.
Mike C
Mike C
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6573 Visits: 1172
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
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25943 Visits: 12475
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
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6573 Visits: 1172
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
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25943 Visits: 12475
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
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 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
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1709 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)
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29929 Visits: 19009
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