using a function created list as a SQL IN list

[ghbeers]

I have a list of keys in a function (XTSTSEC) that I create in order to prevent duplicate rows from being reported. These keys are dependent on a course. So I would like to have something like this work, but it does not.

select *
from COURSE_SECTIONS CS, COURSE_SECTIONS_LS CSL

where CS.COURSE_SECTIONS_ID = '33644'
and CS.COURSE_SECTIONS_ID = CSL.COURSE_SECTIONS_ID
and CSL.SEC_MEETING IN (dbo.XTSTSEC (CS.COURSE_SECTIONS_ID))

I've tried to make the list with single quotes, without quotes, etc. When there is only one in the list (when I removed the quotes) and I change the IN to = it works.

Is something like this possible. I am limited to they environment I'm in what I can do with creating these functions (virtual fields) from the database.

 

[Andrzejek]

What does your function XTSTSEC return?
Could you show the sample outcome?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[ghbeers]

Oh, I forgot that. I've tried formats like '1234','2345','3456' and 1234,2345,3456 and neither work.

 

[Andrzejek]

So your data looks like:

[pre]
SEC_MEETING [blue]
1234
2345
3456 [/blue][/pre]

And the SQL like this does NOT work....?
[tt]
select *
from COURSE_SECTIONS CS, COURSE_SECTIONS_LS CSL
where CS.COURSE_SECTIONS_ID = '33644'
and CS.COURSE_SECTIONS_ID = CSL.COURSE_SECTIONS_ID
and CSL.SEC_MEETING IN ([blue]1234, 2345, 3456[/blue]))
[/tt]
Do you have corresponding data in COURSE_SECTIONS ?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[DaveInIowa]

I have a feeling your function is a Scalar-valued function and not a Table-valued function that it appears you actually need.

Change your function to return a table. Of course the actual table returned needs your own logic and should be filtered by the input parameter.

CREATE FUNCTION [dbo].[XTSTSEC] 
(	
	@CourseSectionsId VARCHAR(16)
)
RETURNS TABLE 
AS
RETURN 
(
	SELECT '1234' SecMeeting
	UNION
	SELECT '2345'
	UNION
	SELECT '3456'
)

Then use the function in your WHERE clause like this...

...
AND CSL.SEC_MEETING IN (SELECT SecMeeting FROM dbo.XTSTSEC (CS.COURSE_SECTIONS_ID))