Re: The LIKE operator in a subquery

Rob,

    Thank you Soooo much..a great help !! I tried the first method repeatedly and just could NOT get the darn thing to work. SQL*Plus just did not like the syntax. The second method DID work fine. I was quite amazed to see that you could but a comma after a FROM clause to augment the SELECT list...very cool trick. Thanks again for taking the time to help..much appreciated.

"rob" <rob_at_dsdelft.nl> wrote in message news: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 Tue Oct 02 2001 - 01:06:29 CEST