I've written code in SQL to pull out the data from two separate tables and join them together. I'm trying to qualify my results with an IN statement, so that only trades with certain counterparties show up.
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 2".COUNTERPARTYNAME,
"Trade Details".TERM
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)) ) and
("Trade Details 2".COUNTERPARTYNAME IN ('A', 'B', 'C'))
ORDER BY "Trade Details 2".COUNTERPARTYNAME asc
However, when I run this query in DB Browser for SQL, only the trades with COUNTERPARTY 'A', appear, even though i know we have trades with counterparties B and C as well. What am I doing wrong?
Your SQL 'IN' clause is correct. Well, theoretically, at least.
There are a number of problems that could be causing something like this - and it's tough to tell without you actually reducing the problem down to a minimalist example and including sample data.
Some possibilities:
A) You don't actually have CounterParty 'B' - at least, not that also matches the rest of your Where clauses. You could identify if this is the case by simply removing that 'CounterParty' section of the query, and actually verifying that there are indeed 'B' lines that aren't being removed.
B) Your data isn't justified/padded/etc how you expect. Have any spaces before the 'B' in those rows? Then your 'IN' clause isn't going to match.
C) You've got a bug in your Where clause that's removing valid rows. I mean, take a look at these segments:
substr('20180602', 0, 5)
substr('20180602', 5,2)
substr('20180602', 7, 9)
... what on earth? The second one looks correct. The third one might work, but only because there are only two characters to get starting at the 7th position (and they're the two characters you want) But I have a hard time believing that the first of those substr is actually doing what you want.
Honestly, that whole date comparison is just horrible. I mean, you're assembling a date string... by reassembling a different date string that's already a string literal. Huh? Why not just use:
and ("Trade Details 2".MATURITY_DATE > DATE('2018-06-02'))
Anyway, I get the feeling that you didn't really try too hard to solve this problem on your end before posting here. How many different ways did you tweak the WHERE clauses to figure out what was filtering out your data?