Re: Like Predicate and Sub-Query

Works like a charm! Thomas - you da man!

Thanks
Siclaro

"Thomas J. Kyte" wrote:

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

--
'I am not; therefore I don't think.'