Between DATE and TODAY-CodePudding

On Oracle SQL Developer I am trying to use this code below as base. However, with this code all the possible data are shown, but I would like to filter the results

WHERE oh.ship_by_date BETWEEN '01/01/2021' AND current date

(In system DATE is shown as dd/mm/yyyy)

I tried to put this filter but I do not understand why the results are still coming out with all ship by dates, hence are not filtered. Where would I have to put the filter, and how could I write it correctly? Thank you so much for your help.

select  
  q1.order_id, q1.status,  q1.ship_by_date, q1.shipped_date,
  count(q1.order_id) lines, sum(q1.qty_ordered) qty, 
  q1.user_def_num_4, q1.user_def_note_2, q1.name, q1.order_type, q1.seller_name, 
  sum(q1.qty_shipped), 
  q1.work_group, q1.creation_Date
from
(
  SELECT 
    OH.ORDER_ID, OH.STATUS, TO_CHAR( OH.SHIP_BY_DATE, 'YYYY-MM-DD')  ship_by_date,
    TO_CHAR(OH.SHIPPED_DATE,'YYYY-MM-DD') shipped_date, ol.QTY_ORDERED, 
    oh.user_def_num_4, oh.user_def_note_2, oh.name, oh.order_type, ol.qty_shipped,
    oh.seller_name, oh.creation_date, oh.work_group
  FROM V_ORDER_HEADER Oh
  left JOIN v_order_line ol on oh.order_id = ol.order_id
  union all
  (
    SELECT 
      OH.ORDER_ID, OH.STATUS,  TO_CHAR( OH.SHIP_BY_DATE, 'YYYY-MM-DD')  ship_by_date,
      TO_CHAR(OH.SHIPPED_DATE,'YYYY-MM-DD') shipped_date, ol.QTY_ORDERED, 
      oh.user_def_num_4, oh.user_def_note_2, oh.name, oh.order_type, ol.qty_shipped,
      oh.seller_name, oh.creation_date, oh.work_group
    FROM V_ORDER_HEADER_archive Oh
    left JOIN v_order_line_archive ol on oh.order_id = ol.order_id
    union
    SELECT 
      OH.ORDER_ID, OH.STATUS, TO_CHAR( OH.SHIP_BY_DATE, 'YYYY-MM-DD')  ship_by_date,
      TO_CHAR(OH.SHIPPED_DATE,'YYYY-MM-DD') shipped_date, ol.QTY_ORDERED, 
      oh.user_def_num_4, oh.user_def_note_2, oh.name, oh.order_type, ol.qty_shipped,
      oh.seller_name, oh.creation_date, oh.work_group
    FROM V_ORDER_HEADER_archive_2 Oh
    left JOIN v_order_line_archive ol on oh.order_id = ol.order_id
  )
) q1
group by 
  q1.order_id, q1.status, q1.ship_by_date,  q1.shipped_date, 
  q1.NAME, q1.order_type, q1.user_def_num_4, q1.user_def_note_2, q1.seller_name,
  q1.work_group, q1.creation_Date

Thank you.

CodePudding user response：

There are two ways that immediately come to mind. In both cases i would recommend attempting to cast your ship_by_date as a datetime and then comparing against your (hardcoded?) 2021 date and current date.

E.g:

WHERE CAST(oh.ship_by_date AS datetime) 
BETWEEN '2021-01-01 00:00:00.000' AND GETDATE()

WHERE CAST(oh.ship_by_date AS datetime) > '2021-01-01 00:00:00.000' 
and CAST(oh.ship_by_date AS datetime) < GETDATE()

EDIT - I just realised you are also turning your oh.ship_by_date to a CHAR using TO_CHAR in your selects. Try taking that part out too. E.g.

select  q1.order_id, q1.status ,  q1.ship_by_date, q1.shipped_date,
count(q1.order_id) lines, sum(q1.qty_ordered) qty, q1.user_def_num_4, q1.user_def_note_2, q1.name, q1.order_type, q1.seller_name, sum(q1.qty_shipped), q1.work_group, q1.creation_Date
from
(SELECT OH.ORDER_ID, OH.STATUS, OH.SHIP_BY_DATE, TO_CHAR(OH.SHIPPED_DATE,'YYYY-MM-DD') shipped_date, ol.QTY_ORDERED, 
oh.user_def_num_4, oh.user_def_note_2, oh.name, oh.order_type, ol.qty_shipped, oh.seller_name, oh.creation_date, oh.work_group
FROM V_ORDER_HEADER Oh
left JOIN v_order_line ol on oh.order_id = ol.order_id
--where oh.order_id = '0082693218') */
union all
(SELECT OH.ORDER_ID, OH.STATUS, OH.SHIP_BY_DATE, TO_CHAR(OH.SHIPPED_DATE,'YYYY-MM-DD') shipped_date, ol.QTY_ORDERED, 
oh.user_def_num_4, oh.user_def_note_2, oh.name, oh.order_type, ol.qty_shipped, oh.seller_name, oh.creation_date, oh.work_group
FROM V_ORDER_HEADER_archive Oh
left JOIN v_order_line_archive ol on oh.order_id = ol.order_id
union
SELECT OH.ORDER_ID, OH.STATUS, OH.SHIP_BY_DATE, TO_CHAR(OH.SHIPPED_DATE,'YYYY-MM-DD') shipped_date, ol.QTY_ORDERED, 
oh.user_def_num_4, oh.user_def_note_2, oh.name, oh.order_type, ol.qty_shipped, oh.seller_name, oh.creation_date, oh.work_group
FROM V_ORDER_HEADER_archive_2 Oh
left JOIN v_order_line_archive ol on oh.order_id = ol.order_id))
--where oh.order_id = '0082693218') */
q1

WHERE CAST(oh.ship_by_date AS datetime) 
BETWEEN '2021-01-01 00:00:00.000' AND GETDATE()

group by q1.order_id, q1.status, q1.ship_by_date,  q1.shipped_date, 
q1.NAME, q1.order_type, q1.user_def_num_4, q1.user_def_note_2, q1.seller_name, q1.work_group, q1.creation_Date

CodePudding user response：

You can place the following WHERE clause with every query part, i.e. after each LEFT JOIN clause.

WHERE oh.ship_by_date >= DATE '2021-01-01' AND oh.ship_by_date < TRUNC(SYSDATE)   1

(The 1 is short for INTERVAL '1' DAY by the way.)

The other option would be to place the following WHERE clause after unioning all the tables (i.e. after ) q1.

WHERE q1.ship_by_date >= DATE '2021-01-01' AND q1.ship_by_date < TRUNC(SYSDATE)   1

But this would probably be much slower, because you are dealing with all the data first rather than just with the date range you are interested in, and then only in the very end you apply that date range limit. So, while this alternative may be considered more readable and better maintainable, it is probably still a bad idea.

Then, you are using a constant 2021-01-01 here. Will this be still the same value when you run the query next year? If you want the current year instead, you can use

WHERE oh.ship_by_date >= TRUNC(SYSDATE, 'YEAR') AND oh.ship_by_date < TRUNC(SYSDATE)   1

At last a remark on your data: You are dealing with history tables here. The more current entries are in V_ORDER_HEADER and V_ORDER_LINE. The older rows have been moved to V_ORDER_HEADER_ARCHIVE, V_ORDER_HEADER_ARCHIVE_2 and V_ORDER_LINE_ARCHIVE. From the names all these are views, not tables, so I don't know what magic happens behind the scenes here. Generally it is often a better idea to work with partitioned tables, so you work with only one table instead of with a current table and a history table in your query and the DBMS then accesses the partitions it needs to fulfill the task, which is usually only the current partition(s), while old partitions are seldom accessed. And old data can be removed much quicker by merely dropping old partitions.