I am working with the database of a journal entry voucher management system. I have a journal_entry table with 517 rows. I select the table with a jev no = '2022-03-001'
SELECT
jev_no, account_code, debit, credit
FROM
journal_entry
WHERE
jev_no ='2022-03-001'
and the returned rows are:
jev_no | account_code | debit | credit |
---|---|---|---|
2022-03-001 | 50501010 | 800.00 | 0.00 |
2022-03-001 | 20201010 | 0.00 | 800.00 |
2022-03-001 | 10102010 | 0.00 | 521.00 |
2022-03-001 | 20201070 | 521.00 | 0.00 |
I want to filter these specific values in the table:
but if a row doesn't match the condition, it should still be included in the result
Then I tried to filter the table with
SELECT
j.jev_no, j.account_code, j.debit, j.credit
FROM
journal_entry j
WHERE
(account_code = 10102010 and j.credit > 0)
AND account_code <> 20201010
AND (account_code = 20201070 and j.credit > 0)
AND ( account_code <> 50299990 and j.credit > 0)
AND (account_code <> 50213040 and j.credit > 0)
AND (account_code <> 10305010 and j.credit > 0)
and jev_no = '2022-03-001'
But there are no rows returned.
Result should be:
jev_no | account_code | debit | credit |
---|---|---|---|
2022-03-001 | 50501010 | 800.00 | 0.00 |
2022-03-001 | 10102010 | 0.00 | 521.00 |
Because:
The account code
20201070
should be excluded because the credit
value is not greater than 0.
The account code
20201010
should also be excluded.
Include
and Exclude
are mutually exclusive in this case. From your intention, you have 2 include account_code
and 4 exclude account_code
. What about the rest of the account_code which are not covered by those 6 ? Should you include or exclude the rest? Based on your request but if a row doesn't match the condition, it should still be included in the result ,it means you would like to include the rest, then what use are those 2 include account_code
for? Why not just have the 4 exclude account_code
and include the rest ? From my point of view, we should use a blacklist approach (specify the exclude account_code
and include the rest) here.
SELECT
jev_no, account_code, debit, credit
FROM
journal_entry
WHERE
jev_no ='2022-03-001'
and (not ( account_code = 20201010
or ( account_code = 50299990 and credit > 0)
or (account_code = 50213040 and credit > 0)
or (account_code = 10305010 and credit > 0)
)
)
;
-- result set:
# jev_no, account_code, debit, credit
2022-03-001, 50501010, 800.00, 0.00
2022-03-001, 10102010, 0.00, 521.00
2022-03-001, 20201070, 521.00, 0.00
Last, please check your expected output, as it does not add up based on your rules.