Roi
I have managed to sort the problem, by taking the join out.
I haven't fully checked this yet, but I think it's right. The join was to one other table, and in the SELECT I was filtering it to only pick up certain records from the 2nd table.
The 2nd table is only a lookup table (code,desc), and if the user doesn't pick an option from the lookup table, the SELECT won't pick it up, because it is filtering the whole view, when I only wanted it to filter on the 2nd table.
Here is the original code;
SELECT careplan.*,ccmslook.lk_desc;
FROM database!careplan LEFT OUTER JOIN database!ccmslook;
ON careplan.lk_code = ccmslook.lk_code;
WHERE ccmslook.lk_type = "AB"
Here is what I think it should have been;
SELECT careplan.*,ccmslook.lk_desc;
FROM database!careplan LEFT OUTER JOIN database!ccmslook;
ON careplan.lk_code = ccmslook.lk_code;
WHERE (ccmslook.lk_type = "AB" OR EMPTY(careplan.lk_code))
The latter code works fine.
So there it is.
Thanks for your help, Kev.
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only