Diagnosing issues with SQL Queries that return no results

Diagnosing SQL Issues

1. Get the SQL query from debugs (you should already have this to have determined it returns no results).

2. Decode the entities - https://coderstoolbox.net/string/#!encoding=xml&action=decode&charset=us_ascii

3. Copy to a text editor and add double line breaks before each: SELECT, JOIN (of any type), FROM, GROUP BY, ORDER BY, WHERE

4. Check the core table (the one from the 'main' FROM) and see what sec_id or ids are on the records you were expecting to see.

5. Check the permissions of those records manually - or if there is a sub-query to SELECT the sec_ids then manually run that query with 'AND [qualification].sec_id IN([list of expected sec_ids])' - if the user doesn't have permissions from the manual check or the query returns no results it is permissions.

6. Remove the 'WHERE' clauses individually until you start getting results.

7. Change any inner joins to left joins on the innermost query and run it manually - check for any NULLs in the results that are on a field from a joined table - they are likely to be caused by the JOIN not returning anything - manually check the fields being joined on on the records you were expecting to see (in both left and right sides of the join).

TODO: Fine tune this and see how much could be automated.