Adding a single line of code deletes my entire table SQL

Question

I'm attempting to fix some of the dates I have in my SQL table. For context, I joined the two tables, "Trade Details" and "Trade Details 2" together. One of the columns in "Trade Details 2" is "START_DATE" with dates ranging back to the early 2000's in the format 2010-05-08. I'm looking to make every START_DATE before 2018-06-22 into 2018-06-22. Currently, my code is below:

SELECT "Trade Details 2".Portfolio, 
"Trade Details 2".CONTRACT_ID, 
DATE("Trade Details 2".START_DATE) as START_DATE, 
DATE(substr("Trade Details 2".MATURITY_DATE, 0, 5) || '-' || substr("Trade 
Details 2".MATURITY_DATE, 5,2) || '-' ||    substr("Trade Details 
2".MATURITY_DATE, 7, 9)) as MATURITY_DATE, 
"Trade Details 2".NOTIONAL1,
"Trade Details 2".CONTRACT_NPV,
"Trade Details".TERM,
MAX(date('2018-06-22'), date("Trade Details 2".start_date))
FROM "Trade Details 2" 
JOIN "Trade Details" 
WHERE "Trade Details 2".CONTRACT_ID="Trade Details".FCC_ID and
("Trade Details 2".NOTIONAL1 > "0.0") and
("Trade Details 2".MATURITY_DATE > DATE(substr('20180602', 0, 5) || '-' || 
substr('20180602', 5,2) || '-' ||    substr('20180602', 7, 9)) )
ORDER BY CONTRACT_ID asc

This in theory should work I think, but for some reason it's not. I added the max(date) line and my table went from 19000 rows to 0. That's the only thing I changed, adding that row to fix the dates, and that completely eliminated my table. Did I write the max line wrong?

Is it actually deleting records from the table or is it simply not returning the rows you expect? — Justin Pearce, Jul 09 '18 at 19:43
Then it's not returning what you expect. "0 rows returned" simply means it did not find anything meeting your criteria. — Justin Pearce, Jul 09 '18 at 21:08

score 0 · Answer 1 · answered Jul 09 '18 at 21:19

If adding the line:

MAX(date('2018-06-22'), date("Trade Details 2".start_date))

Caused the issue, you may consider the inverse of the logic:

MIN(date('2018-06-22'), date("Trade Details 2".start_date))

Since you want to find START_DATE less than or equal to 2018-06-22. MAX() will return the largest value of an aggregate, in this case it would return everything with a START_DATE equal to or greater that your target. MIN() returns the smallest value in an aggregate. Additionally, since you are doing:

DATE("Trade Details 2".START_DATE) as START_DATE

...you can simplify your query by using it like:

MIN(date('2018-06-22'), START_DATE)

Adding a single line of code deletes my entire table SQL

1 Answers1