At first I helped the testers understand what inputs you need to give the query to get it to find records. But the testers said that they still were unable to get a single result record.
Unfortunately the test environment is on lock down. I cannot log in and run SQL queries myself to check up on the data. So I kept e-mailing the testers, trying to get them to run SQL for me to diagnose the problem.
After many queries I came to the conclusion that the application had to be able to get some results. So as a last effort I had them run the big SQL code that the app runs. That's when I found out the whole big query results in an ORA-01502. In other words "index unusable". I normally run the big SQL myself when diagnosing problems. But in this case I skipped it because I needed to get somebody else to run it. From now on I will just have to get them to key in or cut-n-paste all the SQL in the first place.
The good news is that broken indexes can be fixed the DBA Team. This leaves me free to get back to other tasks on hold. See you next time.