Re: Like Predicate and Sub-Query

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/06/03
Message-ID: <8hbq12$vqc$1@nnrp1.deja.com>#1/1

In article <39386BFA.B416F8C8_at_dfw.net>,
  Siclaro <siclaro_at_dfw.net> wrote:
> Hello
>
> I am wondering if a subquery can feed the LIKE predicate in a where
> clause. I'll give an example:
>
> Let say I have a query:
> select name from table where name like 'JOHN%;
>
> but I want the value for the like to be a return value of a sub-
 select,
> as in
>
> Select name from table where name like (select name || '%' from table
> where empid = 12345);
>
> Can this be done in Oracle 7.3.3? It seems like I have done this
 before
> but cannot get it to work. Any suggestions would be helpful.
>
> Thanks
> Siclaro
>
>

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
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Sat Jun 03 2000 - 00:00:00 CDT