Question

我被困在我的卡片/Oracle query上。

对这个问题,感兴趣的领域为:、_day (td.ws_date)、ot.valid_from和ot.valid_to.。

There is one record returned for each effective_day. The record is valid as long as it was edited on or before the effective_day and on or after the effective_day. To find out when it was valid we look at the fieldsot.valid_from and ot.valid_to.

ot.valid_from <= effective_day <= ot.valid_to

缩略语可在一天多时间更新valid_toandvalid_from。相应更新这些领域。因此,在三天的时间里,一项命令的更新可能希望:

ID |Order   | Valid From         | Valid To           |
=================================================================
0  |112     | 07-SEP-11 21:13:12 | 08-SEP-11 01:02:11 |
1  |112     | 08-SEP-11 01:02:12 | 08-SEP-11 01:14:12 |
2  |112     | 08-SEP-11 01:14:13 | 09-SEP-11 05:23:51 |
3  |112     | 09-SEP-11 05:23:52 | 09-SEP-11 16:21:13 |
4  |112     | 09-SEP-11 16:21:14 | null               |

If I want to return the order detals for effective_day = 08-SEP-11. The data returned should be the most recently updated data for that day. So it should be ID 2.

Similarly, if I want to return the order details for effective_day = 09-SEP-11 then ID 4 should be returned.

这里,我

SELECT  
  td.ws_date effective_date,
  ot.ORDER_NO,
  ot.PROCESS_AREA,
  ot.VALID_FROM,
  ot.VALID_UNTIL
  FROM ws_tracker_dates_tab td, ws_tracker_old_tab ot 
  WHERE (ot.VALID_FROM  <= td.WS_DATE)
  AND (ot.VALID_UNTIL IS NULL or (ot.VALID_UNTIL>=td.ws_date))
  AND ot.COMPLETED =  N 
  AND td.WS_DATE BETWEEN (SYSDATE -30) AND (SYSDATE)
  AND ot.process_area is not null

Any help would be appreciated.

Answer 1

To avoid problems like that, use a future date (like 31-DEC-9999) as the "valid-to" date for the current active/valid entry.

That way, you can always use between() to locate the latest entry. Also ORDER BY will behave correctly.

如果您能够改变这一状况,就形成一种观点,将<代码>null<>/code>改为:

select ..., coalesce(valid_to, to_timestamp( 31-DEC-9999 ,  DD-MON-YYYY )), ....

Answer 2

Use a subquery in the where clause like the following

Where ot.ID = 
    (select max(in1.ID) 
    from ws_tracker_old_tab in1 where in1.Order=ot.Order 
    and in1.VALID_FROM = (select max(in2.VALID_FROM) 
                      from ws_tracker_old_tab in2 
                      where in2.Order =in1.Order 
                      and in2.VALID_FROM  <= td.WS_DATE 
                      and (in2.VALID_UNTIL IS NULL or in2.VALID_UNTIL>=td.ws_date))

如果你知道评价数据库(FROM)的日期总是每个顺序独一无二,那么你就能够做到一点简单:

Where ot.VALID_FROM = (select max(in1.VALID_FROM) 
                       from ws_tracker_old_tab in1 
                       where in1.Order = ot.Order 
                       and in1.VALID_FROM  <= td.WS_DATE 
                       and (in1.VALID_UNTIL IS NULL or in1.VALID_UNTIL>=td.ws_date))

Answer 3

The easiest way to deal with valid_from and valid_to (especially in consideration that valid_to may be null [which should likely mean hival]), is to ignore valid_to: Because in this context, it s a derived value (something that s not usually supposed to be stored in a database).

尽管如此,我认为你应当努力:

WITH max_stamp(effective_date, order_no, stamp) as (
               SELECT a.ws_date, b.order_no, b.MAX(b.valid_from)
               FROM ws_tracker_dates_tab a
               JOIN ws_tracker_old_tab b
               ON b.valid_from < CAST((a.ws_date + INTERVAL  1  DAY) AS TIMESTAMP)
               WHERE a.ws_date BETWEEN (SYSDATE - 30) AND (SYSDATE)
               GROUP BY a.ws_date, b.order_no)
SELECT a.*, b.effective_date
FROM ws_tracker_old_tab a
JOIN max_stamp b
ON b.stamp = a.valid_from    
AND b.order_no = a.order_no

请注意,Im on DB2, and haven t usedOracle,从而可能需要作一些调整。

友情链接