Select Query in Oracle giving issue

Question

Query:

select * from v_fi_trans_logs 
where case_num like '105'
AND TRANS_DT=CAST('09-JUL-2014' AS DATE);

Doesn't return me any records.

But If I do a select as

select TRANS_DT,CAST('09-JUL-2014' AS DATE) from v_fi_trans_logs where case_num like '105'

It returns me values which is equal.

    TRANS_DT   CAST('09-JUL-2014' AS DATE) 
----------------------------------------------    
    09-JUL-14   09-JUL-14
    09-JUL-14   09-JUL-14
    09-JUL-14   09-JUL-14
    09-JUL-14   09-JUL-14
    09-JUL-14   09-JUL-14
    09-JUL-14   09-JUL-14
    09-JUL-14   09-JUL-14

Can anyone help me out, with the issue here.

Any inputs will be of great help.

score 0 · Accepted Answer · answered Jul 10 '14 at 10:05

0

I found the solution by HIT and TRAIL method :

It worked with:

select * from v_fi_trans_logs 
where case_num like '105'
AND TRANS_DT LIKE CAST('09-JUL-2014' AS DATE);

That is,replacing "=" with "LIKE" solves the issue.

answered Jul 10 '14 at 10:05

Sashi Kant

13,277
9
44
71

If you are using `like` oeprator, make sure you have something like `like '105%'`. Of course when you are using like operator, it does a full table scan of all rows which result in more time for execution of sql statement. – Jacob Jul 10 '14 at 10:09

score 0 · Answer 2 · edited May 23 '17 at 12:06

I think that the problem is that you are using :

like '105' AND TRANS_DT=CAST('09-JUL-2014' AS DATE);

You can use like as a comparation of numbers and strings… only once.

But if you are trying to compare ´105´ AND TRANS_DT=CAST('09-JUL-2014' AS DATE) logically. You cannot do it. The translation will be ( 7 & true ) for example.

Check out this solution the error is very similar. How can I use "OR" condition in MySQL CASE expression?

score 0 · Answer 3 · answered Jul 10 '14 at 22:12

Are you sure TRANS_DT is truncated? If it has a time component it will not work when compared to a date.

Try the following:

select * 
  from v_fi_trans_logs 
 where case_num like '105'
   and trunc(trans_dt) = date '2014-07-09';

Or if you have an index on trans_dt that you want to use:

where case_num like '105'
  and trans_dt >= date '2014-07-09'
  and trans_dt <  date '2014-07-10'

Select Query in Oracle giving issue

3 Answers3