Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

How to join a table-valued Function Expand / Collapse
Author
Message
Posted Tuesday, October 16, 2007 6:48 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135
Example removed at Sergiy's request.
Post #411524
Posted Wednesday, October 17, 2007 4:15 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, October 13, 2014 8:08 PM
Points: 4,573, Visits: 8,353
Mike,
unfortunately there is no such thing as "CROSS APPLY" in MS SQL 2000.
Post #411636
Posted Wednesday, October 17, 2007 1:10 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 1:15 PM
Points: 14, Visits: 100
Thanks a lot for your time and help....
I'll give it a try.

Post #411893
Posted Wednesday, October 17, 2007 5:33 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135
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?
Post #411974
Posted Wednesday, October 17, 2007 7:11 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, October 13, 2014 8:08 PM
Points: 4,573, Visits: 8,353
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.
Post #411989
Posted Wednesday, October 17, 2007 7:27 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135
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!
Post #411993
Posted Wednesday, October 17, 2007 8:19 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, October 13, 2014 8:08 PM
Points: 4,573, Visits: 8,353
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.
Post #412009
Posted Tuesday, April 1, 2008 5:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 6, 2009 9:51 AM
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..
Post #477515
Posted Wednesday, April 2, 2008 9:58 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 9:05 PM
Points: 441, Visits: 933
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]
Post #478655
Posted Wednesday, April 2, 2008 10:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:08 PM
Points: 7,105, Visits: 15,432
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?
Post #478700
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse