DB2 SQL技术求助：筛选非费用类支付的最新交易日期

Got it, let's fix this up to get exactly the result you need: just the latest date for normal (non-fee) payments, formatted like 12-07-2018.

Correct SQL Query #

First, here's the simplified, working query that hits your target:

-- Basic version to get the latest date (adjust formatting based on your database)
SELECT MAX(a.RTVLDT) AS Last_Transaction_Date
FROM data a
WHERE a.TXFT NOT IN ('Fee', 'Fee pay');

If your database returns a date format that doesn't match dd-mm-yyyy, use the appropriate formatting function for your SQL dialect:

• MySQL/MariaDB:

  SELECT DATE_FORMAT(MAX(a.RTVLDT), '%d-%m-%Y') AS Last_Transaction_Date
  FROM data a
  WHERE a.TXFT NOT IN ('Fee', 'Fee pay');
  

• SQL Server:

  SELECT CONVERT(VARCHAR, MAX(a.RTVLDT), 105) AS Last_Transaction_Date -- 105 = dd-mm-yyyy
  FROM data a
  WHERE a.TXFT NOT IN ('Fee', 'Fee pay');
  

• Oracle:

  SELECT TO_CHAR(MAX(a.RTVLDT), 'DD-MM-YYYY') AS Last_Transaction_Date
  FROM data a
  WHERE a.TXFT NOT IN ('Fee', 'Fee pay');
  

Why Your Original Query Wasn't Working #

Let's break down the issues with your existing code:

• Invalid CASE WHEN syntax: You forgot the END clause required for every CASE statement, and your logic was targeting fee transactions instead of excluding them.
• Unnecessary subquery and grouping: You don't need to group by every column or use a subquery here—we just need an aggregate (MAX()) on filtered records.
• Incomplete filtering: You weren't explicitly excluding the "Fee pay" value, which was part of your requirement.

Step-by-Step Explanation #

1. Filter out fee transactions: The WHERE TXFT NOT IN ('Fee', 'Fee pay') clause removes all records explicitly marked as fees, leaving only your normal payments (regardless of their random codes).
2. Get the latest date: MAX(RTVLDT) grabs the most recent date from the filtered set of normal transactions.
3. Format the date (if needed): The date formatting functions ensure the output matches the 12-07-2018 format you specified.

Edge Case Note #

If your TXFT column can have NULL values (and those NULLs represent normal payments), adjust the WHERE clause to include them:

WHERE (a.TXFT != 'Fee' AND a.TXFT != 'Fee pay') OR a.TXFT IS NULL

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