Toad World® Forums

Help with parameters on query

why does this return 0 rows


and this
select EQUIP_OPERATOR_ID from equip where EQUIP_OPERATOR_ID in ('BAG-V-00271', 'NAR-V-10992')
return two rows

Bind variables don't work with 'in' lists.

Morning All,

Bind variables don't work with 'in' lists.

What John said is true, however what I think John meant is that you cannot user a single bind variable to hold an inlist, you must split it each inlist entry out as a separate bind, as follows.

SELECT EQUIP_OPERATOR_ID
FROM EQUIP
WHERE EQUIP_OPERATOR_ID in (:vtns_1, :vtns_2);

That will work. You will get prompted for the bind values on execution, so all you do is choose each one in turn and give it the desired bind value, then click OK to execute the query. You will get your two EQUIP_OPERATOR_IDs returned - assuming you used valid bind values of course!

An inlist is not actually a list of values, it is a table of values. There are plenty of functions which can return a table of values from a string. These work using the PIPE command which allows the function to pipe each "row" out to the caller.

You will recognise these functions as they are called in a particular way:

SELECT EQUIP_OPERATOR_ID
FROM EQUIP
WHERE EQUIP_OPERATOR_ID in TABLE(myFunction('oper_id, oper_id2));

They are pretty simple to write yourself, if necessary, and are useful in converting a VARCHAR2 with a lot of, for example, comma separated values, to a format that the IN operator can use.

HTH

Thanks Norm. Your reply was much more helpful than mine. :smiley:

Morning All,

[jdorlon] jdorlon https://forums.toadworld.com/u/jdorlon Quest
October 1

Bind variables don't work with 'in' lists.

What John said! However what John means is that you cannot user a single
bind variable to hold an in list, you must split it out as follows.

You need to set up two (or as many as desired) bind variables, vtns_1
and vtns_2 for example, then use the query as:

SELECT EQUIP_OPERATOR_ID
FROM EQUIP
WHERE EQUIP_OPERATOR_ID in (:vtns_1, :vtns_2);

That will work. You will get prompted for the values, choose each one
and give it the desired bind value, then OK to execute the query. You
will get your two EQUIP_OPERATOR_IDs returned - assuming you used valid
bind values of course!

An inlist is not actually a list of values, it is a table of values.
There are plenty of functions which can return a table of values from a
string. These work using the PIPE command which allows the function to
pipe each "row" out to the caller.

You will recognise these functions as they are called in a particular way:

SELECT EQUIP_OPERATOR_ID
FROM EQUIP
WHERE EQUIP_OPERATOR_ID in TABLE(myFunction('oper_id, oper_id2));

They are pretty simple to write yourself, if necessary, and are useful
in converting a VARCHAR2 with a lot of, for example, comma separated
values, to a format that the IN operator can use.

HTH

Hmm. Weird stuff happening here. Two replies!

You know me John, always sticking my nose in! :wink:

Cheers,
Norm.

Maybe toad cannot handle those binds, sqlplus can:

SQL>select 1 from dual where '1' in (&&z);
Enter value for z: '2','1','3'

         1
----------
         1

SQL>select Q'[&z]' as z_value from dual;

Z_VALUE
-----------
'2','1','3'

Also when sending to script as parameter (i.e. &1, &2...etc), it should be sent like
"'2','1','3'"
Hope this helps...

Of course, when you undefine &&Z value and test with values that has no "1" value, it returns no rows ...as it should be.

SQL>select 1 from dual where '1' in (&&z);
Enter value for z: '2','0','3'

no rows selected

Thank you
your explanations made it clear