passing parameters to function w/ inner join

  • ok i am sure this is simple but i may be over thinking this i am a noob to functions.

    I have a function that calculates distance etc based on latitude and longitude from a table.

    I am trying to create a stored proc that will give me all the zip codes within X number of miles. I have all this set up in the function but am not sure how to pass the parameters to the function

    my functions work just fine if i execute them and manually put in the parameters values, i am not sure how to get it to work via a joined query. right

    I basically want to pass the value of the lat and lon from my zipusa table to my function like this Ufn_GetLocationsInRadius(ZIPUSA.LAT,ZIPUSA.LNG,25)

    now i get

    Cannot find either column "zipcodes" or the user-defined function or aggregate "dbo.Ufn_GetLocationsInRadius", or the name is ambiguous

    I tried something like this I may be way off

    SELECT mytable1.dbo.app_TelephoneDetails.FirstName, mytable1.dbo.app_TelephoneDetails.LastName, mytable1.dbo.app_TelephoneDetails.City,

    mytable1.dbo.app_TelephoneDetails.State, mytable1.dbo.app_TelephoneDetails.ZIPCode,

    Ufn_GetLocationsInRadius(ZIPUSA.LAT,ZIPUSA.LNG,25)as result,Ufn_GetLocationsInRadius.distance,

    ZIPUSA.LAT, ZIPUSA.LNG, ZIPUSA.LAT_RADIANS, ZIPUSA.LNG_RADIANS

    FROM mytable1.dbo.app_TelephoneDetails

    INNER JOIN

    ZIPUSA ON mytable1.dbo.app_TelephoneDetails.ZIPCode = ZIPUSA.ZIP_CODE

    where mytable1.dbo.app_TelephoneDetails.ZIPCode='12345'

    [/code]

    FUNCTION [dbo].[Ufn_GetLocationsInRadius](@latitude [float], @longitude [float], @radius [int])

    RETURNS TABLE (

    [citystname] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [state] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [zipCode] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [distance] [float] NULL

    ) WITH EXECUTE AS CALLER

    AS EXTERNAL NAME [ZipCodeRadius].[ZipCodeRadius.LocationsInRadiusFunction].[InitMethod]

    [/code]

  • you are using the table values function in a wrong manner. create your function as a scalar function.then it should work fine. let us know if you still have problems


    Everything you can imagine is real.

  • You missed the fact that the function returns all zips within a specified radius. It needs to be TBV'd.

    Try this...

    SELECT

    mytable1.dbo.app_TelephoneDetails.FirstName,

    mytable1.dbo.app_TelephoneDetails.LastName,

    mytable1.dbo.app_TelephoneDetails.City,

    mytable1.dbo.app_TelephoneDetails.State,

    mytable1.dbo.app_TelephoneDetails.ZIPCode,

    Ufn_GetLocationsInRadius.distance,

    ZIPUSA.LAT,

    ZIPUSA.LNG,

    ZIPUSA.LAT_RADIANS,

    ZIPUSA.LNG_RADIANS

    FROM

    mytable1.dbo.app_TelephoneDetails

    INNER JOIN ZIPUSA

    ON mytable1.dbo.app_TelephoneDetails.ZIPCode = ZIPUSA.ZIP_CODE

    INNER JOIN Ufn_GetLocationsInRadius(ZIPUSA.LAT,ZIPUSA.LNG,25)T2

    on T2.ZIPCODE = ZIPUSA.ZIPCODE,

    where mytable1.dbo.app_TelephoneDetails.ZIPCode='12345'

    Doug

  • i tried dougs solution but i get

    Msg 4104, Level 16, State 1, Line 1

    The multi-part identifier "ZIPUSA.LAT" could not be bound.

    Msg 4104, Level 16, State 1, Line 1

    The multi-part identifier "ZIPUSA.LNG" could not be bound.

    Msg 4104, Level 16, State 1, Line 1

    The multi-part identifier "Ufn_GetLocationsInRadius.distance" could not be bound.

  • Gail, can you post the schema for ZIPUSA


    Everything you can imagine is real.

  • Msg 4104, Level 16, State 1, Line 1

    The multi-part identifier "Ufn_GetLocationsInRadius.distance" could not be bound.

    for this error to disappear use T2 in the select


    Everything you can imagine is real.

  • ok i got rid of the error for the ufn by using the t2 i still get the other ones relating to zipusa

    here is schema

    CREATE TABLE [dbo].[ZIPUSA](

    [ZIPID] [int] IDENTITY(1,1) NOT NULL,

    [ZIP_CODE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [CITYSTNAME] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [STATE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [LAT] [real] NULL,

    [LNG] [real] NULL,

    [LAT_RADIANS] AS (radians([LAT])) PERSISTED,

    [LNG_RADIANS] AS (radians([LNG])) PERSISTED,

    [SOUNDEX_CITYNAME] AS (soundex([CITYSTNAME])) PERSISTED,

    [County] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [ZipClass] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    CONSTRAINT [PK_ZIPCODESUSA] PRIMARY KEY CLUSTERED

    (

    [ZIPID] ASC

    )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

    ) ON [PRIMARY]

    thanks

  • forgot to say and can you also post the schema for

    mytable1.dbo.app_TelephoneDetails


    Everything you can imagine is real.

  • How accurate do you need this to be?

    If a few miles off one way or the other is okay, the way I would do this is a reverse distance lookup. This won't be exact, but it's probably good enough for most uses.

    Distance north-south is approximately 69 miles per degree of latitude. Distance east-west varies the further you are from the equator, but if you estimate it the same way, you won't be too far off. (Distances in the continental US, for example, vary from nearly 60 miles per degree in southern Texas to just about 45 miles per degree at the Canadian border.)

    So, if you want to quickly find what Zip codes are within X miles of each other, divide by 69, and find out which ones are within that latitude-longitude range.

    Let's say you're starting with latitude 47.61, longitude -122.34 (which happens to be in Seattle, WA), and you want to find all Zip codes within 100 miles.

    100/69 = 1.45

    So any Zip code that lists as latitude between 46.16 and 49.06 and also between longitude -120.89 and -123.79, is within about 100 miles of that original location.

    It's not precise, but it's not off by much either. And since Zip code lat/long data is based (in most cases) on the post office that services the Zip code, which isn't very precise, it's probably close enough for most work.

    If you need exact, then you'll need data on the borders of all Zip codes (which can be very irregular shapes), and you'll need to pre-calculcate as much of it as possible.

    If you need slightly more precise than this, but not exact, then use the usual lat-long of the post office, and pre-calculate all distances. It's approximately 1.7 billion combinations (based on 41,000 Zip codes). You'll need good indexes on that if you want any speed on it.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • If you're happy with your current function, look up Apply in Books Online. It tells you how to "join" to a UDF with input parameters based on the columns of the table it's being joined to.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Hey GS, I like those approximations. I don't think we're talking interplanetery landings here so it should be close enough. btw, when I've used a "locate store" function on a website it does usually put me at the main Post Office in that zip.

    DAB

  • GSquared (4/24/2008)


    How accurate do you need this to be?

    Let's say you're starting with latitude 47.61, longitude -122.34 (which happens to be in Seattle, WA), and you want to find all Zip codes within 100 miles.

    100/69 = 1.45

    So any Zip code that lists as latitude between 46.16 and 49.06 and also between longitude -120.89 and -123.79, is within about 100 miles of that original location.

    I am with you on everything you said except for this 100/69=1.45 ok

    Then the next line you say any zip that show lat between 46.16 and 49.06...

    How did you arrive at that conclusion by getting the 1.45?

    Thanks

  • bledu (4/24/2008)


    forgot to say and can you also post the schema for

    mytable1.dbo.app_TelephoneDetails

    I did not create this table as i would had done it a little differently...

    CREATE TABLE [dbo].[app_TelephoneDetails](

    [TelephoneID] [int] IDENTITY(1,1) NOT NULL,

    [Telephone] [nchar](10) NOT NULL,

    [FirstName] [nchar](100) NOT NULL,

    [LastName] [nvarchar](255) NULL CONSTRAINT [DF_app_TelephoneDetails_LastName] DEFAULT (N'None'),

    [Address] [nchar](256) NULL,

    [LastModified] [datetime] NULL,

    [StatusCode] [nchar](10) NULL,

    [LastUser] [nvarchar](256) NULL,

    [Active] [bit] NULL,

    [Title] [nvarchar](255) NULL,

    [Gender] [nvarchar](255) NULL,

    [OfficeName] [nvarchar](255) NULL,

    [City] [nvarchar](255) NULL,

    [State] [nvarchar](255) NULL,

    [ZIPCode] [nchar](10) NULL,

    [CountyName] [nvarchar](255) NULL,

    [MSAName] [nvarchar](255) NULL,

    [PrimarySpecialty] [nvarchar](255) NULL,

    [SecondarySpecialty] [nchar](255) NULL,

    [HomeAddress] [nvarchar](max) NULL,

    [HomeCity] [nvarchar](255) NULL,

    [HomeState] [nvarchar](255) NULL,

    [HomeZIP] [nchar](10) NULL,

    [HomePhone] [nchar](10) NULL,

    [Disp] [nvarchar](50) NULL,

    [RoleName] [varchar](256) NULL,

    [Email] [nvarchar](255) NULL,

    [CellPhone] [nvarchar](10) NULL,

    [DateAvailable] [datetime] NULL,

    CONSTRAINT [PK_app_TelephoneDetails] PRIMARY KEY CLUSTERED

    (

    [TelephoneID] ASC

  • SQLServerLifer (4/24/2008)


    You missed the fact that the function returns all zips within a specified radius. It needs to be TBV'd.

    Try this...

    SELECT

    mytable1.dbo.app_TelephoneDetails.FirstName,

    mytable1.dbo.app_TelephoneDetails.LastName,

    mytable1.dbo.app_TelephoneDetails.City,

    mytable1.dbo.app_TelephoneDetails.State,

    mytable1.dbo.app_TelephoneDetails.ZIPCode,

    Ufn_GetLocationsInRadius.distance,

    ZIPUSA.LAT,

    ZIPUSA.LNG,

    ZIPUSA.LAT_RADIANS,

    ZIPUSA.LNG_RADIANS

    FROM

    mytable1.dbo.app_TelephoneDetails

    INNER JOIN ZIPUSA

    ON mytable1.dbo.app_TelephoneDetails.ZIPCode = ZIPUSA.ZIP_CODE

    INNER JOIN Ufn_GetLocationsInRadius(ZIPUSA.LAT,ZIPUSA.LNG,25)T2

    on T2.ZIPCODE = ZIPUSA.ZIPCODE,

    where mytable1.dbo.app_TelephoneDetails.ZIPCode='12345'

    Doug

    I don't think that you can use a Table-valued function using row values from a table in an INNER join, you have to call it using the CROSS APPLY functionality.

    Meaning:

    SELECT

    mytable1.dbo.app_TelephoneDetails.FirstName,

    mytable1.dbo.app_TelephoneDetails.LastName,

    mytable1.dbo.app_TelephoneDetails.City,

    mytable1.dbo.app_TelephoneDetails.State,

    mytable1.dbo.app_TelephoneDetails.ZIPCode,

    Ufn_GetLocationsInRadius.distance,

    ZIPUSA.LAT,

    ZIPUSA.LNG,

    ZIPUSA.LAT_RADIANS,

    ZIPUSA.LNG_RADIANS

    FROM

    mytable1.dbo.app_TelephoneDetails

    INNER JOIN ZIPUSA

    ON mytable1.dbo.app_TelephoneDetails.ZIPCode = ZIPUSA.ZIP_CODE

    CROSS APPLY Ufn_GetLocationsInRadius(ZIPUSA.LAT,ZIPUSA.LNG,25) T2

    where mytable1.dbo.app_TelephoneDetails.ZIPCode='12345'

    ----------------------------------------------------------------------------------
    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?

  • hey matt 2 things

    one when executing the original query you posted i get

    The multi-part identifier "Ufn_GetLocationsInRadius.distance" could not be bound.

    for the line in the select statement not the cross apply i tried changing it to dbo.ufn... same error.

    For the moment I commented out that line and now

    your query executed but did not return any results

    I happen to know there are 7 entries for the zip code 70737.

    I wondered if because the zipcodes in the telephone table are nchar and the ones in the zipusa are nvar char if that may cause an issue with the join. I tried converting the telephone zip in the join but i still got no results.

    SELECT

    mytable1.dbo.app_TelephoneDetails.FirstName,

    mytable1.dbo.app_TelephoneDetails.LastName,

    mytable1.dbo.app_TelephoneDetails.City,

    mytable1.dbo.app_TelephoneDetails.State,

    mytable1.dbo.app_TelephoneDetails.ZIPCode,

    --dbo.Ufn_GetLocationsInRadius.distance,

    ZIPUSA.LAT,

    ZIPUSA.LNG,

    ZIPUSA.LAT_RADIANS,

    ZIPUSA.LNG_RADIANS

    FROM

    mytable1.dbo.app_TelephoneDetails

    INNER JOIN ZIPUSA

    ON mytable1.dbo.app_TelephoneDetails.ZIPCode = ZIPUSA.ZIP_CODE

    CROSS APPLY Ufn_GetLocationsInRadius(ZIPUSA.LAT,ZIPUSA.LNG,25) T2

    where mytable1.dbo.app_TelephoneDetails.ZIPCode='70737'

Viewing 15 posts - 1 through 15 (of 42 total)

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