A few days ago, I was trying to debug some code which I had added to an existing procedure. The idea was to check whether a given part had ever been returned by a customer, so the code was something like
... AND NOT EXISTS (SELECT 1 FROM DOCUMENTS WHERE TYPE = 'N' AND ...
The code seemed correct but didn't work. I opened a WinDBI window to check my code, and of course it worked properly there. What could be wrong?
The answer came, as always, when I was doing something else (maybe I went to make a cup of tea) - check whether the DOCUMENTS table had been linked. Should this be the case, there will only be a record or two in this table, so it's fairly obvious that the record which I'm checking for won't be in the (linked) DOCUMENTS table and so the above will return true.
DOCUMENTS was indeed linked - but as it was at the beginning of the procedure, the line wasn't displayed on the screen and as I wrote, I was adding this check to code which I wrote some time ago and had forgotten the small details. No wonder I had forgotten to check this. The fix, of course, was simple
... AND NOT EXISTS (SELECT 1 FROM DOCUMENTS ORIG WHERE TYPE = 'N' AND ...
One simply gives the table an alias which ensures that the real DOCUMENTS table will be used. There are no links in WinDBI so the real table will always be used. This problem is similar to the one described here, so the moral is if something doesn't work in a procedure but does work in WinDBI, check for linked tables. It's so easy to forget.