| gill123 |
Posted: Jun 20, 2007 07:31:19 PM |
Total Post: 12
Joined: Jun, 2007
|
Plz let me know whether the following statement is true or false:
"A single row subquery cannot be used in a condition where the LIKE operator is used for comparison."
Thanks.
|
|
|
 chinna |
| Posted: Jun 21, 2007 02:26:40 AM |  |
|
Total Post: 28
Joined: May, 2007
|
hi,
a single row subquery can be used in where clause.
example dept table has one corresnding row with deptno as 10 and dname as accounting.deptno is pk in table dept and foreign key in emp.
so the query as follows.
select * from emp where deptno=(select deptno from dept where dname='ACCOUNTING');
THE SUBQUERY RETURNS A SINGLW ROW CORRESPONDING ROWS FROM EMP TABLE ARE DISPLAYED.
BYE
SRAWAN
|
|
|
|
|
| gill123 |
| Posted: Jun 21, 2007 03:36:26 AM | |
|
Total Post: 12
Joined: Jun, 2007
|
Yes but can you use a LIKE operator when you are using a single or multiple row subquery as a condition ??
|
|
|
|
|
| gps |
| Posted: Jun 21, 2007 04:38:32 AM | |
|
Total Post: 38
Joined: May, 2006
|
Yes, you can use single row subquery with LIKE operator as follows:
select * from emp where deptno like (select deptno from dept where loc='NEW YORK')
select * from emp where deptno like '%'||(select deptno from dept where loc='NEW YORK')||'%'
Gurwinder
|
|
|
|
|
| gps |
| Posted: Jun 21, 2007 04:40:36 AM | |
|
Total Post: 38
Joined: May, 2006
|
You can not do like this if your inner query is returuing multi-rows.
Gurwinder
|
|
|
|
|
| KS |
| Posted: Jun 21, 2007 04:53:46 AM | |
|
Total Post: 63
Joined: Nov, 2006
|
Its False,
Look at the following query :
SQL> select count(1)
from emp
where JOB like '%'||(select distinct Job
from emp
where job='SALESMAN')||'%';
COUNT(1)
----------
4
This works fine.
-sri
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
