select membfilt.memb_no, sharx.last_pay, NVL(loans.last_loan, CTOD("")) AS last_loan ; from membfilt join sharx on membfilt.memb_no = sharx.memb_no ; left outer join loans on membfilt.memb_no = loans.memb_no INTO CURSOR lapsed ; where loans.last_loan <= dlDate ; ORDER BY 1produced 2912 records and the result set was correct.
select membfilt.memb_no, sharx.last_pay, NVL(loans.last_loan, CTOD("")) AS last_loan ; from membfilt join sharx on membfilt.memb_no = sharx.memb_no ; left outer join loans on membfilt.memb_no = loans.memb_no ; AND loans.last_loan <= dlDate ; INTO CURSOR lapsed ; ORDER BY 1I get 4626 records and some of the last_loan values are blank dates, when in the original loans file they are dates later than the value in variable dlDate. So 1) it produces an incorrect value in that field and 2) they shouldn't be in the results set anyway.
select membfilt.memb_no, sharx.last_pay, NVL(loans.last_loan, CTOD("")) AS last_loan ; from membfilt join sharx on membfilt.memb_no = sharx.memb_no ; left outer join loans on membfilt.memb_no = loans.memb_no ; INTO CURSOR allsharx select * FROM allsharx INTO CURSOR lapsed WHERE last_loan <= dlDate ORDER BY 1Is there a way to do this in one SQL command?