Help with parameters on query

Toad for Oracle & DevOps Toolkit
1

why does this return 0 rows

2019-09-30_16-18-09
2019-09-30_16-18-09.jpg699×509 42.8 KB

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

2

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

3

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

4

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

5

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

6

Hmm. Weird stuff happening here. Two replies!

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

Cheers,
Norm.

7

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

8

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
9

Thank you
your explanations made it clear

10

@JohnDorlon , @jcclegg

I know it is an old thread but I wanted to share a 'trick' for using a single variable for multiple values.
It might to be the best performance-wise, but it works:

In the original poster example, instead of using :

Variable :vtns = 'BAG-V-00271', 'NAR-V-10992'
Query : select EQUIP_OPERATOR_ID from equip where EQUIP_OPERATOR_ID in (:vtns)

Use the following:

Variable :vtns = |BAG-V-00271|NAR-V-10992|
Query : select EQUIP_OPERATOR_ID from equip where :vtns like (CONCAT(CONCAT('%|', EQUIP_OPERATOR_ID ) ,'|%'))

This will give you the same result as

select EQUIP_OPERATOR_ID from equip where EQUIP_OPERATOR_ID in ('BAG-V-00271', 'NAR-V-10992')

Cheers !

1 Like