OraFAQ Forum: SQL & PL/SQL » different outputs on same parameter (datatype date) while using 'like' and '='

Home » SQL & PL/SQL » SQL & PL/SQL » different outputs on same parameter (datatype date) while using 'like' and '=' (oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

different outputs on same parameter (datatype date) while using 'like' and '=' [message #631044]

Thu, 08 January 2015 06:04

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

i want to find total number of employees hired in 1981, I tried below queries
but meanwhile i ran one query which made me think why it is not working when
everything seems fine.

>select count(*) from emp
where to_char(hiredate,'yyyy')=1981 ;
output:- count(*)
10

>select count(*) from emp
where ename like '%S%';
output:- count(*)
4

>select count(*) from emp
where hiredate like %1981%;
output:- count(*)
0

my question is why last query returned 0 , when it was supposed to return 10 .

Report message to a moderator

Re: different outputs on same parameter (datatype date) while using 'like' and '=' [message #631045 is a reply to message #631044]

Thu, 08 January 2015 06:15

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

The last query is invalid, so it is supposed to return an error.

Even if you fix it, you won't get 10 as a result because Oracle stores date values in a format which is not recognizable to us, humans, so the correct way is to use TO_CHAR function (note that you forced Oracle to use implicit datatype conversion in your first SELECT), or EXTRACT (YEAR FROM date) or any other valid option.

Report message to a moderator

Re: different outputs on same parameter (datatype date) while using 'like' and '=' [message #631046 is a reply to message #631044]

Thu, 08 January 2015 06:35

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

jgjeetu wrote on Thu, 08 January 2015 17:34

>select count(*) from emp
where ename like '%S%';
output:- count(*)
4

Interesting. If you are using the standard EMP table in SCOTT schema for the test case, you should get 5 not 4.

Anyway, to understand further what LF told you about storing DATEs, please read this article by Ed Stevens, But I want to store the date in format .....

Report message to a moderator

Re: different outputs on same parameter (datatype date) while using 'like' and '=' [message #631047 is a reply to message #631045]

Thu, 08 January 2015 06:40

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

@LF now i got it thanks for clearifying
the correct query is:-

SELECT COUNT(*) FROM EMP
WHERE TO_CHAR(HIREDATE,'YYYY') LIKE '%1981%'

[Updated on: Thu, 08 January 2015 06:41]

Report message to a moderator

Re: different outputs on same parameter (datatype date) while using 'like' and '=' [message #631048 is a reply to message #631046]

Thu, 08 January 2015 06:42

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

@lalit , i have made few changes on emp table . Laughing

Report message to a moderator

Re: different outputs on same parameter (datatype date) while using 'like' and '=' [message #631051 is a reply to message #631048]

Thu, 08 January 2015 06:47

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Your screen doesn't display the "Add code" icon, isn't it?

Report message to a moderator

Re: different outputs on same parameter (datatype date) while using 'like' and '=' [message #631052 is a reply to message #631047]

Thu, 08 January 2015 06:47

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

jgjeetu wrote on Thu, 08 January 2015 13:40

@LF now i got it thanks for clearifying
the correct query is:-

SELECT COUNT(*) FROM EMP
WHERE TO_CHAR(HIREDATE,'YYYY') LIKE '%1981%'

No, the correct query is

SELECT COUNT(*) FROM  EMP
WHERE extract(year from HIREDATE) = 1981
/

Report message to a moderator

Re: different outputs on same parameter (datatype date) while using 'like' and '=' [message #631054 is a reply to message #631052]

Thu, 08 January 2015 06:55

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

@michel thanks for the reply , i learnt something new today through your query.

now check this screenshot Smile

Attachment: hiredate.JPG
(Size: 30.05KB, Downloaded 1305 times)

Report message to a moderator

Re: different outputs on same parameter (datatype date) while using 'like' and '=' [message #631056 is a reply to message #631054]

Thu, 08 January 2015 08:40

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

And?

Note: you don't need to post an image just to post the text, just use copy and paste but enclose it between code tags.

Report message to a moderator

Re: different outputs on same parameter (datatype date) while using 'like' and '=' [message #631058 is a reply to message #631056]

Thu, 08 January 2015 08:54

Bill B
Messages: 1971
Registered: December 2004

Senior Member

when you have a 4 digit string it makes no sense to use wildcards

SELECT COUNT(*) FROM EMP
WHERE TO_CHAR(HIREDATE,'YYYY') = '1981';

[Updated on: Thu, 08 January 2015 08:55]

Report message to a moderator

Re: different outputs on same parameter (datatype date) while using 'like' and '=' [message #631059 is a reply to message #631056]

Thu, 08 January 2015 08:55

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

OP is allergic to code tags, hence he prefers attaching images rather than actual code in text format.

Report message to a moderator

Re: different outputs on same parameter (datatype date) while using 'like' and '=' [message #631063 is a reply to message #631058]

Thu, 08 January 2015 09:42

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Bill B wrote on Thu, 08 January 2015 09:54

when you have a 4 digit string it makes no sense to use wildcards

I would use neither:

SELECT COUNT(*) FROM EMP
WHERE TO_CHAR(HIREDATE,'YYYY') = '1981';

nor:

SELECT COUNT(*) FROM  EMP
WHERE extract(year from HIREDATE) = 1981
/

since neither would use index on hiredate. We should code in such way that Oracle could use index if such index will be needed in the future. So I'd use:

SELECT COUNT(*) FROM  EMP
WHERE HIREDATE >= DATE '1981-01-01'
  AND HIREDATE < DATE '1982-01-01'
/

Compare:

SQL> create index emp_idx1 on emp(hiredate);

Index created.

SQL> set linesize 132
SQL> explain plan for
  2  SELECT COUNT(*) FROM EMP
  3  WHERE TO_CHAR(HIREDATE,'YYYY') = '1981';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 2083865914

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     8 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |     1 |     8 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
---------------------------------------------------

   2 - filter(TO_CHAR(INTERNAL_FUNCTION("HIREDATE"),'YYYY')='1981')

14 rows selected.

SQL> explain plan for
  2  SELECT COUNT(*) FROM  EMP
  3  WHERE extract(year from HIREDATE) = 1981
  4  /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 2083865914

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     8 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |     1 |     8 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
---------------------------------------------------

   2 - filter(EXTRACT(YEAR FROM INTERNAL_FUNCTION("HIREDATE"))=1981)

14 rows selected.

SQL> explain plan for
  2  SELECT COUNT(*) FROM  EMP
  3  WHERE HIREDATE >= DATE '1981-01-01'
  4    AND HIREDATE < DATE '1982-01-01'
  5  /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 1016503744

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |     8 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |          |     1 |     8 |            |          |
|*  2 |   INDEX RANGE SCAN| EMP_IDX1 |     2 |    16 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
---------------------------------------------------

   2 - access("HIREDATE">=TO_DATE(' 1981-01-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "HIREDATE"<TO_DATE(' 1982-01-01 00:00:00',
              'syyyy-mm-dd hh24:mi:ss'))

16 rows selected.

SQL>

SY.

Report message to a moderator

Previous Topic:	Need to get only decimal values upto 2 digits if we have nothing in decimal points that has to be 0
Next Topic:	how to loop and duplicate a string

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 01:17:15 CDT 2024