Joining an inline table function? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Transact-SQL (2000)

Joining an inline table function?

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2004-12-10 : 13:20:34

I'm pulling my hair out here. The actual query is very big, so I'm going to try simplifying it here to break out the main issue.

Basically, I need to include two columns from an inline table function in an otherwise normal select. Essentially:


create table users(username nvarchar(32),latitude float,longitude float)

insert into users (username,latitude,longitude) VALUES ('a',1,2)
insert into users (username,latitude,longitude) VALUES ('a',3,4)

select username,latitude,longitude,f.city,f.state
from users
join dbo.f_LatLonCityState(latitutde,longitude) f

create function f_LatLonCityState(lat float,lon float) 
returns table
as
begin
  -- In reality, this would be a select against some GIS data
  return(select 'test city','test state')
end

Obviously that's very simplified, and just as obviously, it doesn't work at all (for one thing, there's no ON clause for the join). The intent is to have the function look up the city/state that a given latitude/longitude falls into.

The only ways I can get anything remotely like this to work are to either have two seperate subqueries in the main select (one for city, one for state, ugly!) or to use a table variable, insert into it, and then update it after... also ugly.

Am I just missing something here? Is there some general purpose solution for looking up two or more values based on the values of two or more columns, all in one query?

Thanks
-b

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-10 : 13:30:37

you need a join key between users and function to do that.
or make a function return a varchar that has city and state in it.

Go with the flow & have fun! Else fight the flow

aiken
Aged Yak Warrior

525 Posts

Posted - 2004-12-10 : 13:40:59

Thanks for the reply!

I've tried passing the username field into the function and having it return it in the table to use as a join key, however that doesn't work either -- "join f_LatLonCityState(username,latitude,longitude) f on f.username=users.username" throws the error "unknown optimizer hint 'username'".

I've considered making the function return a comma delimted list and then parsing that again in the main query, but that seems ridiculous. Surely there's a way to do this for real?

Cheers
-b

X002548
Not Just a Number

15586 Posts

Posted - 2004-12-10 : 14:29:59

Why not just use a sproc?

Brett

8-)

aiken
Aged Yak Warrior

525 Posts

Posted - 2004-12-10 : 14:59:00

Well, this is happening inside a sproc. But even inside there, I need to do this lookup for each row of the returned results.

So far, I've been reduced to using a table variable to store the results of the initial query,then doing two seperate updates on that table variable to get essentially the same data from the lat/lon table. It's ugly and would be slow under load, but at least it works.

Cheers
-b

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-10 : 16:27:20

you would need to return a single value in your UDF, but either a) call it twice or b) return both results, but parse them.

then you'd say:

select username,latitude,longitude, dbo.GetCity(latitude, longitude) as city, dbo.GetState(latitude, Longitude) as state
from users

or -- you could have a parameter that the function takes which returns either a city or state, and make it one funcion.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources