Home » SQL & PL/SQL » SQL & PL/SQL » plz tell diff. b/w these quries
plz tell diff. b/w these quries [message #208134] Fri, 08 December 2006 05:09 Go to next message
amit_vass2000
Messages: 52
Registered: December 2006
Location: Delhi
Member
Why query No. 1 is not giving rows

1-SQL> select * from emp where ename in ('A%','B%');

no rows selected



2-SQL> select * from emp where substr(ename,1,1) in ('A','B');

EMPNO ENAME JOB MGR HIREDATE SAL DEPTNO COMM
--------- ---------- --------- --------- --------- --------- --------- ---------
7499 ALLEN SALESMAN 7698 20-FEB-81 6000 30 600
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 285
7876 ADAMS CLERK 7788 12-JAN-83 3800 20
7488 ANGELINA ANALYST 7558 17-MAY-99 2000 90
7558 Amit Analyst 456 15-MAY-78 19775 90
Re: plz tell diff. b/w these quries [message #208136 is a reply to message #208134] Fri, 08 December 2006 05:12 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
'%' and '_' need to be used with 'LIKE'.
where ename like 'A%'
   or ename like 'B%'
By
Vamsi
Re: plz tell diff. b/w these quries [message #208137 is a reply to message #208134] Fri, 08 December 2006 05:13 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK, this IS a Newbies Forum, but you can't expect someone to take your hand and lead you through Oracle SQL path. I believe you should take the manual, read it as long as it takes and figure certain things out by yourself.

Because, seeing questions you ask, it seems that you have no idea what you are doing.
Re: plz tell diff. b/w these quries [message #208139 is a reply to message #208137] Fri, 08 December 2006 05:17 Go to previous messageGo to next message
amit_vass2000
Messages: 52
Registered: December 2006
Location: Delhi
Member
Sorry SIr, But the actual matter was that one of my junior want to know views on this query, i was not asking the sol. of the query. i also know how to use like and in. But i was just asking why query no. 1 is showing no rows.
Re: plz tell diff. b/w these quries [message #208142 is a reply to message #208139] Fri, 08 December 2006 05:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This query
select * from emp where ename in ('A%','B%');
will fetch all the rows from EMP where ename is either 'A%' or 'B%'. It will not do a wildcard search.

I feel that this knowedge is implied by your claim to 'know how to use like and in'
Re: plz tell diff. b/w these quries [message #208165 is a reply to message #208139] Fri, 08 December 2006 07:05 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
one of your juniors ?! Shocked
Previous Topic: Filtering rows
Next Topic: search redundant record
Goto Forum:
  


Current Time: Thu Dec 08 22:08:36 CST 2016

Total time taken to generate the page: 0.14472 seconds