Document Type | Technical Information
Category | APP Development
Applicable Product Version | T6, T7
Document Number | TDETI012
Overview
In Tibero, the IN clause can be used without any specific restriction on the number of conditions.
Method
1. Composite comparison available
<Example>
SQL> CREATE TABLE TEST (NO NUMBER, NAME VARCHAR(10));
SQL> INSERT INTO TEST
SELECT LEVEL, 'NAME'||LEVEL FROM DUAL CONNECT BY LEVEL < 100001;
SQL> COMMIT;
SQL> SELECT * FROM TEST WHERE NO IN (1,2,...omitted,29999,30000);
NO NAME
---------- ----------
1 NAME1
2 NAME2
...omitted
29999 NAME29999
30000 NAME30000
SQL> SELECT * FROM TEST WHERE (NO,NAME) IN ((1,'NAME1'),(2,'NAME2'),
...omitted
(29999,'NAME29999'),(30000,'NAME30000'));
NO NAME
---------- ----------
1 NAME1
2 NAME2
...omitted
29999 NAME29999
30000 NAME30000
2. Char semantic comparison available
<Example>
SQL> CREATE TABLE TEST (NO NUMBER, CHA CHAR(10));
SQL> INSERT INTO TEST
SELECT LEVEL, 'CHA'||LEVEL FROM DUAL CONNECT BY LEVEL < 1000001;
SQL> COMMIT;
SQL> SELECT * FROM TEST WHERE CHA IN ('CHA1','CHA2',
...omitted
'CHA29999','CHA30000');
NO CHA
---------- ----------
1 CHA1
2 CHA2
...omitted
29999 CHA29999
30000 CHA30000
SQL> SELECT * FROM TEST WHERE (NO,CHA) IN ((1,'CHA1'),(2,'CHA2'),...omitted
(29999,'CHA29999'),(30000,'CHA30000'));
NO NAME
---------- --------------------
1 CHA1
2 CHA2
...omitted
29999 CHA29999
30000 CHA30000
3. When the DATA TYPE of the column in the IN condition does not change
<Example>
SQL> CREATE TABLE TEST (NO NUMBER, NAME VARCHAR(10));
SQL> INSERT INTO TEST VALUES (1,1);
SQL> INSERT INTO TEST VALUES (2,2);
SQL> INSERT INTO TEST VALUES (3,3);
SQL> SELECT * FROM TEST WHERE NAME IN (1,2,3);
NO NAME
---------- ----------
1 1
3 3
SQL> INSERT INTO TEST VALUES (4,'A');
SQL> SELECT * FROM TEST WHERE NAME IN (1,2,3);
TBR-5074: Given string does not represent a number in proper format.
-> When only numbers are in the IN list, automatic type conversion is performed and output is shown,
but if there is a value that cannot be converted to a number, execution is not possible.
Caution
If the above conditions are not met, the general expression restrictions apply.
(There is a limit of 65535 temporary values required when evaluating a single expression.)
In this case, the number of limits will vary depending on the use of expressions.