Need help about cross join - 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 2005 Forums

Transact-SQL (2005)

Need help about cross join

Author

Topic

Ali.M.Habib
Yak Posting Veteran

54 Posts

Posted - 2009-07-01 : 03:00:31

I have table named Ranges


CREATE TABLE [dbo].[RANGES](
	[Name] [varchar](50) NOT NULL,
	[From] [int] NULL,
	[To] [int] NULL
)

then i want to use function that get the range of numbers between each from, to value

I typed this query using cross join


select * from RANGES
cross join
(select *  from dbo.fnCrossJoinRange2([Ranges.from],[Ranges.to]))t

dbo.fnCrossJoinRange2 is a table value function that I use
this error appear after running the query

Msg 207, Level 16, State 1, Line 3
Invalid column name 'Ranges.from'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'Ranges.to'.

I want to solve that without using cursors or loop
any suggestion please

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-07-01 : 05:10:01

If fnCrossJoinRange2 is function you need to use new Apply clause in SQL 2005.

select * from RANGES
cross apply(select * from dbo.fnCrossJoinRange2([Ranges.from],[Ranges.to]))t

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-01 : 05:23:03

if u want to pass the range table values u should use apply(outer,cross) as ayamas suggested
if u want to pass default value the u ca use cross join
select * from RANGES,
dbo.fnCrossJoinRange2(1,5)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources