Need help about cross join

Question

Need help about cross join

Ali.M.Habib

SSC Eights!

Points: 930
More actions
July 1, 2009 at 1:04 am

#103777

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

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

To pass columns from a table as parameters to a table-valued function in the same FROM clause, you need CROSS APPLY, not CROSS JOIN

select <Column list >

from RANGES

CROSS APPLY dbo.fnCrossJoinRange2([Ranges.from],[Ranges.to]) t

Without seeing the function code, I can't tell if this will do what you want or not, but test it and see.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ali.M.Habib SSC Eights! Points: 930 More actions · Answer 2

GilaMonster (7/1/2009)
To pass columns from a table as parameters to a table-valued function in the same FROM clause, you need CROSS APPLY, not CROSS JOIN
select <Column list >
from RANGES
CROSS APPLY dbo.fnCrossJoinRange2([Ranges.from],[Ranges.to]) t
Without seeing the function code, I can't tell if this will do what you want or not, but test it and see.

thanks for ur help the code of the function can be found :

CREATE VIEW dbo.Digits AS

select 0 as value

union allselect 1 as value

union allselect 2 as value

union allselect 3 as value

union allselect 4 as value

union allselect 5 as value

union allselect 6 as value

union allselect 7 as value

union allselect 8 as value

union allselect 9 as value

GO

ALTER FUNCTION dbo.fnCrossJoinRange2 (

@first int , --##PARAM @first The lowest value in the range.

@Last int --##PARAM @Last The highest value in the range.

)

RETURNS @values TABLE ( value int primary key ) AS

BEGIN

INSERT INTO @values(value)

SELECTnum = units.value +

(tens.value) +

(hundreds.value ) +

(Thousands.value ) +

(TenThousands.value ) +

(CThousands.value ) +

(Millions.value )

FROMdbo.Digits units

CROSS JOIN (SELECT value * 10 as value from dbo.Digits WHERE value * 10 <=@last) tens

CROSS JOIN (SELECT value * 100 as value from dbo.Digits WHERE value * 100 <=@last) hundreds

CROSS JOIN (SELECT value * 1000 as value from dbo.Digits WHERE value * 1000 <=@last) Thousands

CROSS JOIN (SELECT value * 10000 as value from dbo.Digits WHERE value * 10000 <=@last) TenThousands

CROSS JOIN (SELECT value * 100000 as value from dbo.Digits WHERE value * 100000 <=@last) CThousands

CROSS JOIN (SELECT value * 1000000 as value from dbo.Digits WHERE value * 1000000 <=@last) Millions

where units.value +

(tens.value ) +

(hundreds.value) +

(Thousands.value ) +

(TenThousands.value ) +

(CThousands.value ) +

(Millions.value )

BETWEEN @first and @Last

RETURN

END

GO

I used ur code as:

select *

from RANGES CROSS APPLY dbo.fnCrossJoinRange2([Ranges.from],[Ranges.to])

but give the same error

any help in that

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

ali.m.habib (7/1/2009)
select *
from RANGES CROSS APPLY dbo.fnCrossJoinRange2([Ranges.from],[Ranges.to])
but give the same error

Is the database case sensitive? If so, you'll need to fix the case in the proc's parameters.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ali.M.Habib SSC Eights! Points: 930 More actions · Answer 4

GilaMonster (7/1/2009)
ali.m.habib (7/1/2009)
select *
from RANGES CROSS APPLY dbo.fnCrossJoinRange2([Ranges.from],[Ranges.to])
but give the same error
Is the database case sensitive? If so, you'll need to fix the case in the proc's parameters.

thanx alot but the output not give the range , it just repeated the data

the data in ranges is

Ali15

Ali2450

I want the out put to be

Ali1

Ali 2

Ali 3

.............

Ali2 4

Ali

Ali2

Ali.M.Habib SSC Eights! Points: 930 More actions · Answer 5

GilaMonster (7/1/2009)
ali.m.habib (7/1/2009)
select *
from RANGES CROSS APPLY dbo.fnCrossJoinRange2([Ranges.from],[Ranges.to])
but give the same error
Is the database case sensitive? If so, you'll need to fix the case in the proc's parameters.

thanx alot but the output not give the range , it just repeated the data

the data in ranges is

Ali15

Ali2450

I want the out put to be

Ali1

Ali 2

Ali 3

.............

Ali2 4

Ali2 5

...............

Ali250

the output I got from the qur\ery was:

Ali15

Ali2450

do u have any idea to fix that

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Can you give us some sample data for the Ranges table? Read this to see the best way to post this to get quick responses.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ali.M.Habib SSC Eights! Points: 930 More actions · Answer 7

GilaMonster (7/1/2009)
Can you give us some sample data for the Ranges table? Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/

The Ranges Table have the following Data

Name From To

------------------------

Ali 1 5

Ali2 4 50

I want the out put to be the name and the data range between from and to column numbers

Ali 1

Ali 2

Ali 3

.................

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

So want you want outputting is

Ali, 1

Ali, 2

Ali, 3

Ali, 4

Ali, 5

Ali2, 5

Ali2, 6

Ali2, 7

... all the way up to

Ali2, 49

Ali2, 50

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ali.M.Habib SSC Eights! Points: 930 More actions · Answer 9

GilaMonster (7/1/2009)
So want you want outputting is
Ali, 1
Ali, 2
Ali, 3
Ali, 4
Ali, 5
Ali2, 5
Ali2, 6
Ali2, 7
... all the way up to
Ali2, 49
Ali2, 50

yes exactly what I want , please help me in that

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 10

Assuming 2005 +

create table ali

(

code varchar(16),

fromcol integer,

toCol integer

)

go

insert into ali values('ali1',1,5)

insert into ali values('ali2',6,15)

go

with ctenumber(rownum)

as

(

Select rownum = row_number() over(order by id)

from sysobjects

)

select * from ali,

ctenumber

where rownum between fromcol and tocol

Clear Sky SQL
My Blog[/url]

Ali.M.Habib SSC Eights! Points: 930 More actions · Answer 11

Dave Ballantyne (7/1/2009)
Assuming 2005 +
create table ali
(
code varchar(16),
fromcol integer,
toCol integer
)
go
insert into ali values('ali1',1,5)
insert into ali values('ali2',6,15)
go
with ctenumber(rownum)
as
(
Select rownum = row_number() over(order by id)
from sysobjects
)
select * from ali,
ctenumber
where rownum between fromcol and tocol

thanks alot for you code it worked well, but is there any article to describe the code

with ctenumber(rownum)

as

(

Select rownum = row_number() over(order by id)

from sysobjects

)

Also

it not work for high numbers

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 12

Which part are you particularly needing help with the cte (common table expression ) or row_number() ?

Google is your friend ...

Also
it not work for high numbers

If will work for as many rows are returned. If you need high numbers, there are many different solutions.

I personally have a calendar table with a dayoffset integer that i use. You could cross join to sysobjects to itself . The source data dosent really matter , its just a way of getting an incrementing number.

Clear Sky SQL
My Blog[/url]