基于ODUDAT（最后使用日期）与ODCDAT（创建日期）的SQL DELETE语句逻辑修正咨询

Let's walk through correcting your delete logic to match your exact requirements, since the current statement has some critical logical flaws.

First, Clarify the Requirements #

You need to delete rows from HERAPERM.SCRUBLITST only when:

• The last used date (ODUDAT) is older than two months before today, OR
• The last used date is empty (NULL), and the creation date (ODCDAT) is also older than two months before today

Crucially, you want to keep rows where ODUDAT is empty but ODCDAT is within the last two months.

What's Wrong with the Original Statement? #

Your current query has two major issues:

1. Backwards comparison: You're using > instead of <, so you're deleting rows that are newer than two months ago, which is the opposite of what you want.
2. Incorrect logic for NULL ODUDAT: The COALESCE approach lumps ODUDAT and ODCDAT together without accounting for the "keep if ODCDAT is recent" rule. It also relies on string comparison for dates, which can lead to errors (e.g., 123120 (Dec 31, 2020) would be treated as "greater than" 010121 (Jan 1, 2021) in string terms, even though it's an older date).

Corrected DELETE Statement #

This query properly handles both cases and uses date type conversion to avoid string comparison bugs:

DELETE FROM HERAPERM.SCRUBLITST
WHERE 
  -- Case 1: ODUDAT exists and is older than 2 months
  (ODUDAT IS NOT NULL AND 
   TO_DATE(ODUDAT, 'MMDDYY') < CURRENT_DATE - 2 MONTHS)
  OR
  -- Case 2: ODUDAT is NULL, and ODCDAT is older than 2 months
  (ODUDAT IS NULL AND 
   TO_DATE(ODCDAT, 'MMDDYY') < CURRENT_DATE - 2 MONTHS);

How This Works #

• Case 1: For rows with a recorded last use date, we only delete if that date is more than two months old.
• Case 2: For rows with no last use date (ODUDAT is NULL), we only delete if the creation date is also more than two months old. This ensures we keep any recently created files that haven't been used yet.

Example Verification #

Using your sample data:

• A210407001 *FILE DDMF 040821 (ODCDAT=040821, ODUDAT=NULL): If today is June 2021, two months ago is April 2021. 040821 (April 8, 2021) is not older than two months, so this row is kept.
• BYOD_00003 *FILE PF 021521 021621 (ODCDAT=021521, ODUDAT=021621): If today is June 2021, 021621 (Feb 16, 2021) is older than two months, so this row is deleted.

内容的提问来源于stack exchange，提问作者user3465081