Sorry for the bad title. Couldn't really think of a good name without an explanation.
In our system (source is inside Oracle PL/SQL packages) we have quite a lot large SQL queries with a sh..load of variables, dozens of joins to multiple tables and a lot of conditions. Some of those tables and variables are even configurable by the user.
Example would be a warehouse. The user has some orders/positions and items in different boxes. He can see the order needs item_x and he can see that there is a box with item_x in it. When he tries to perform some action he get's an error that item_x for his order is not available.
Most of the time the problem is some configuration or special case that prevents the system for using that box the user has in mind (he isn't telling the system which boxes, the system is searching for a valid box with a lot of ordering etc.).
At the moment a problem like this ends up in a procedure which looks like this:
- Client calls and says action_x isn't working all though he has the item_x that is needed by the order in box 1234
- We connect to the database and check that what he saying is true
- We copy the freakin huge SQL into a worksheet.
- We debug into the procedure to copy all the variables and constants being used in the operation he is performing and replace them (by that time probably 20 minutes have passed).
- Now we can comment out the WHERE-clause or change some JOINs to LEFT JOINs to find the row that was filtered out and the reason why.
Those things can happen after updates because some bug sneaked into the conditions, it can be some configuration problem caused by the user or it is just right that those item wasn't seen but the user can't keep track on all the reasons why, nether can we.
What would be good practices to change this problem. Of course it might be possible to remove the conditions, change everything to LEFT JOINs and validate everything after, while looping over the data, so it is possible to print out a neat message. All though it has to be more like returning a freakin huge result set with reasons behind every row why that wasn't a match. And of course this would lead to really, really bad performance and his application would probably freeze for minutes.
Another solution might be having two queries. One with conditions and INNER JOINs and another without and with LEFT JOINs which only gets executed if the first doesn't return any valid rows. This wouldn't impact performance for working cases but would probably end up in bugs etc. because of redundancy and at the end displaying that for the user would be pretty hard.
And of course we could speed up the search/replace part with some aggressive logging of the variables but at the end that would just speed up the debugging. Are there good solutions for problems like that which might also benefit the user?