Reputation: 145
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:
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.
Hope someone can help, thanks.
Upvotes: 2
Views: 47
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
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