>I actually understand why the SQL failed to return records, but only after debugging it. I was hoping there was a way that the SQL engine could tell me itself. It knows when there is an error, can it tell me why it returned zero records too?
I don't think this is possible, but especially if you need R/O information (for a report), you can divide your SELECTion into several steps. As a simplified example, let's say you now have:
select a.KeyField, a.DateField, a.Field3, b.Field4, b.Field5;
from Table1 a join Table2 b on a.KeyField = b.KeyField;
where DateField >= date() - 30;
into cursor Temp
if _tally = 0
MessageBox("There was some problem selecting records")
endif
You could change this to the following:
select a.KeyField, a.DateField, a.Field3;
from Table1 a;
where DateField >= date() - 30;
into cursor Temp
if _tally = 0
MessageBox("There are no records for the previous month")
return
endif
select a.*, b.Field4, b.Field5;
from Temp a join Table2 b on a.KeyField = b.KeyField;
into cursor Temp2
if _tally = 0
MessageBox("There is no related data in the second table")
endif
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)