INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

using a function created list as a SQL IN list

using a function created list as a SQL IN list

(OP)
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.

RE: using a function created list as a SQL IN list

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.

RE: using a function created list as a SQL IN list

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

RE: using a function created list as a SQL IN list

So your data looks like:

SEC_MEETING 
 1234 
 2345 
 3456  

And the SQL like this does NOT work....? ponder

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 (1234, 2345, 3456))

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.

RE: using a function created list as a SQL IN list

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.

CODE

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

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close