>>But they are not. It seems I had it backwards. It works as long as there is an ORDER BY. With no ORDER BY, the numbers are wild.
>>
>>I ran the query with no grouping to see if I could tell what was happening. What is happening is that without the ORDER BY, this phrase:
>>
>>
>> WHERE b.control_no in ;
>> (SELECT towfky FROM redeem WHERE redeem.dredeemdat>= {01/01/2014} AND redeem.dredeemdat<= {12/31/2014}) ;
>>
>>
>>
>>is responding true and including records where redeem.dredeemdat is empty!
>
>Of course it will include these records.
>You check IF b.control_no in some values between some dates, BUT what happens
>if some b.control_no IS in that list but have empty date?
>It will be included in the result.
Why?
If redeem.dredeemdat is BLANK the subselect does not catch the record, so no towfky is existent
b.control is compared with towfky, on equal or identity if character field
EMPTY b.control will only match EMPTY(towky), not non existent towfky
CREATE CURSOR ca (i1 c(1), d1 date)
INSERT INTO ca VALUES ("1", DATE())
INSERT INTO ca VALUES ("2", {..})
CREATE CURSOR cb (i2 c(1),i3 c(1))
INSERT INTO cb VALUES ("1","A")
INSERT INTO cb VALUES ("2","B")
INSERT INTO cb VALUES ("","C")
SELECT * FROM cb WHERE i2 in (SELECT i1 FROM ca WHERE d1<=DATE()+1 AND d1>=DATE()-1)
INSERT INTO ca VALUES ("", {..})
SELECT * FROM cb WHERE i2 in (SELECT i1 FROM ca WHERE d1<=DATE()+1 AND d1>=DATE()-1)
INSERT INTO ca VALUES ("", DATE())
SELECT * FROM cb WHERE i2 in (SELECT i1 FROM ca WHERE d1<=DATE()+1 AND d1>=DATE()-1)
Words are given to man to enable him to conceal his true feelings.
Charles Maurice de Talleyrand-Périgord
Weeks of programming can save you hours of planning.
OffThere is no place like [::1]