How to pass variable in IN clause

Question

I declared the variable i_unit_attempt_status and I want to pass that variable in to 'In clause'. How can I do that?

INNER JOIN student_unit_attempt sua
on sua.person_id=sei.person_id
   AND sua.course_cd=sei.course_cd
   AND sua.unit_cd=sei.unit_cd
   AND (sua.unit_attempt_status in (l_unit_attempt_status))

Data in the variable will be:

Option 1:'COMPLETED','DISCONTIN','DUPLICATE','ENROLLED','INVALID','UNCONFIRM'
Option 2: 'ENROLLED'

Can you [edit] your question to give an example of the value that `l_unit_attempt_status` has? — MT0, May 16 '20 at 21:45
Does this answer your question? [Using :var with "IN" operator in WHERE clause](https://stackoverflow.com/questions/44128459/using-var-with-in-operator-in-where-clause) — sanitizedUser, May 16 '20 at 23:31

score 0 · Accepted Answer · answered May 17 '20 at 07:57

See this answer.

Use LIKE:

INNER JOIN student_unit_attempt sua
on sua.person_id=sei.person_id
   AND sua.course_cd=sei.course_cd
   AND sua.unit_cd=sei.unit_cd
   AND ','||l_unit_attempt_status||',' LIKE '%,'''||sua.unit_attempt_status||''',%'

Or pass in a collection:

CREATE TYPE status_list IS TABLE OF varchar2(20);

DECLARE
  l_unit_attempt_status status_list := status_list( 'COMPLETED','DISCONTIN','DUPLICATE','ENROLLED','INVALID','UNCONFIRM' );
BEGIN
  SELECT <your_column_list>
  INTO   <variables>
  FROM   table_name sei
         INNER JOIN student_unit_attempt sua
         on sua.person_id=sei.person_id
            AND sua.course_cd=sei.course_cd
            AND sua.unit_cd=sei.unit_cd
            AND sua.unit_attempt_status MEMBER OF l_unit_attempt_status;
END;
/

Or split the list:

INNER JOIN student_unit_attempt sua
on sua.person_id=sei.person_id
   AND sua.course_cd=sei.course_cd
   AND sua.unit_cd=sei.unit_cd
   AND sua.unit_attempt_status in ( SELECT COLUMN_VALUE
                                    FROM   XMLTABLE( l_unit_attempt_status ) )

How to pass variable in IN clause

1 Answers1