CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
user3095083
user3095083

Reputation: 145

Not displaying items which match criteria in Oracle SQL Developer but no error

I have the code below and I used this to display fields from the wh_id of 'MP'

select o.wh_id, o.bill_to_code, d.create_date,
   (case when d.pick_area like 'GPS%' then 'GPS' else d.pick_area
    end) as pick_area,
    count(*) as OUC
from t_order o INNER JOIN
 t_pick_detail d
 on o.order_number = d.order_number
where o.wh_id = 'MP'
group by o.wh_id, o.bill_to_code, d.create_date,
     (case when d.pick_area like 'GPS%' then 'GPS' else d.pick_area
    end)
order by o.bill_to_code

It gave me the following results:

enter image description here

Then once I add in one piece of extra criteria which matches items in the table above.

extra criteria is:

AND d.create_date =  '19-FEB-15'

Full code is now :

select o.wh_id, o.bill_to_code, d.create_date,
   (case when d.pick_area like 'GPS%' then 'GPS' else d.pick_area
    end) as pick_area,
    count(*) as OUC
from t_order o INNER JOIN
 t_pick_detail d
 on o.order_number = d.order_number
where o.wh_id = 'MP' AND d.create_date =  '19-FEB-15'
group by o.wh_id, o.bill_to_code, d.create_date,
     (case when d.pick_area like 'GPS%' then 'GPS' else d.pick_area
    end)
order by o.bill_to_code

Now all the results that I get are shown below, but it makes it seem as though there are no results with that info which there are as you can see from above print screen. I don't get any error though.

enter image description here

Hope someone can help, thanks.

Upvotes: 2

Views: 47

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49122

From a performance point of view, I would not suggest to use TRUNC as it would not use any regular index on the DATE column.

Better use a RANGE condition. It will use the index on the date column and you would see an index range scan rather than a FULL TABLE scan.

For example,

d.create_date >= to_date('19-FEB-15', 'DD-MON-YY')
AND
d.create_date < to_date('19-FEB-15 ', 'DD-MON-YY') + 1

Or,

d.create_date 
BETWEEN to_date('19-FEB-15', 'DD-MON-YY') 
AND     to_date('19-FEB-15 ', 'DD-MON-YY') + 1

If you really want to use TRUNC, then you would have to create a function_based index.

Have a look at this link https://hoopercharles.wordpress.com/2010/03/08/impact-of-the-trunc-function-on-an-indexed-date-column/

Upvotes: 1

user3095083
user3095083

Reputation: 145

Definitely create_date column is storing timestamp also. You once should try trunc(d.create_date) = TO_DATE('19-FEB-15','DD-MON-YY').

This worked Sam thank you :)

Upvotes: 0

Related Questions