Re: The LIKE operator in a subquery

From: rob <rob_at_dsdelft.nl>
Date: Mon, 1 Oct 2001 11:15:03 +0200
Message-ID: <9p9c6n$ml3$1_at_news.tudelft.nl>

Found this reply from Thomas Kyte using www.deja.com. Nice feature btw, never thought of it.

In 8.1 -- yes, it'll work. Before that, you can join. Both equivalent methods are shown below:

ops$tkyte_at_8i> select *
  2 from emp
  3 where ename like ( select ename||'%' Like_Column from emp where empno = 7566 )
  4 /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL
COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- --------

• ---------- 7566 JONES MANAGER 7839 02-APR-81 2975 20

REM The above *will not work* in 8.0 and before.... REM the following will:

ops$tkyte_at_8i>
ops$tkyte_at_8i> select *
  2 from emp, ( select ename||'%' Like_Column from emp where empno = 7566 )
  3 where ename like like_column
  4 /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL
COMM     DEPTNO LIKE_COLUMN

---------- ---------- --------- ---------- --------- ---------- --------

• ---------- ----------- 7566 JONES MANAGER 7839 02-APR-81 2975 20 JONES%

--

Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries Received on Mon Oct 01 2001 - 11:15:03 CEST