sql server programming: Can you use a subquery in a subquery?

I can't seem to get mine to work.

I have a subquery (GetPositionIDByZip) that just returns a list of
PositionIDs. I pass the zipcode I am interested in and a mile range.

GetPostionIDByZip '92610',5

I want to get all the positions with these zip codes.

Select JobID, PositionID, JobDescriptions from Positions where PositionID in
(GetPostionIDByZip '92610',5)

Can this be done?

Thanks,

Tom

"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in
message news:%23hVUz52MFHA.3340@TK2MSFTNGP14.phx.gbl...
[quoted text, click to view]

Sorry,

I am running on Sql Server 2000. GetPositionIDByZip is a stored procedure
(I meant stored procedure in my original post - not subquery).

I did turn it into a function that works fine in the from clause, but not in
the where clause.

Here is the Function
***************************************************************************

(@ZIPCode char(5),
@Miles REAL)
RETURNS @PositionIDs Table (PositionID Int)
As
BEGIN
DECLARE @RowCount int
SELECT @RowCount = Count(*) FROM ZIPCodes WHERE ZIPCode = @ZIPCode AND
CityType = 'D'

if @RowCount > 0
BEGIN
insert @PositionIDs (PositionID)
SELECT
PositionID
FROM
ZIPCodes z, RadiusAssistant(@ZIPCode,@Miles) r ,position p
WHERE
(z.zipcode = p.zipcode) and
z.Latitude <= r.MaxLat
AND z.Latitude >= r.MinLat
AND z.Longitude <= r.MaxLong
AND z.Longitude >= r.MinLong
AND CityType = 'D'
AND ZIPCodeType <> 'M'
AND dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude)
<= @Miles

END
RETURN
END

***************************************************************************

This function works fine as:

SELECT * FROM GetPositionIDByZipFunction('92660',5)

But doesn't work for :

SELECT Distinct JobDescription,JobTitle FROM ftsolutions..position p left
outer join applicantPosition a on (a.PositionID = p.PositionID and Email =
'') where p.ClientID = 1234 and PositionID in
(GetPositionIDByZipFunction('92660',5) order by JobTitle DESC

This works find when I don't have the function in it:

SELECT Distinct JobDescription,JobTitle FROM ftsolutions..position p left
outer join applicantPosition a on (a.PositionID = p.PositionID and Email =
'') where p.ClientID = 1234 order by JobTitle DESC

The error I get is:

'GetPositionIDByZipFunction' is not a recognized function name.

But it was a recognized function name in the first select where I used it in
the From clause.

Can I not use this function in a where clause?

Thanks,

Tom
[quoted text, click to view]

Hi
It's not a subquery but UDF (User Defined Function) , anyway did you get an
error?
USE Northwind
GO

CREATE FUNCTION dbo.get_cust_orders
(
@custid char(5)
)
RETURNS TABLE
AS

RETURN SELECT
*
FROM
Orders
WHERE
CustomerID = @custid
GO

SELECT * FROM Customers WHERE CustomerID
IN (SELECT CustomerID FROM get_cust_orders('VINET'))





[quoted text, click to view]

Is this SQL Server? Is GetPostionIDByZip a stored procedure? If so, you can't use a stored procedure
that way. Turn it into a table valued user defined function instead.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/


[quoted text, click to view]

[quoted text, click to view]

That makes sense.

I had also found I could do it this way:

SELECT Distinct JobDescription,JobTitle FROM ftsolutions..position p left
outer join applicantPosition a on (a.PositionID = p.PositionID and Email =
'') join GetPositionIDByZipFunction(15,92660) g on (p.PositionID =
g.PositionID) where p.ClientID = 1234 order by JobTitle DESC

By moving the function to the join clause, it worked fine. I wasn't sure
why until you mentioned that I could use it anywhere you can use a table.

Thanks,

Tom
[quoted text, click to view]

[quoted text, click to view]

(snip)
[quoted text, click to view]

Hi Tom,

Your syntax is wrong. You can use a table-valued funtion everywhere you
could use a table. But the syntax

....
AND PositionID IN (TableName)
....

wouldn't compile either.

Try changing your code to

....
AND PositionID IN (SELECT *
FROM GetPositionIDByZipFunction('92660',5))
....

Note that I also added an extra closing parenthesis to solve the
mismatch that was in your query.

Best, Hugo
--