1 -test1.prg produces correct incorrect results

a - The fields from the joined tables
(source, carrier & transaction) are empty
b - the tables must be opened first or id_pati is "not found"

2 - test2.prg (below) produces correct results

I have used left outer joins many many times, but this time I am
unsuccessful in getting the correct results. Any or all of the 3 joined tables (source, carrier or transaction) may produce a null result that is why a left join must be used. Also note that after repeated attempts that correct results will occassionally result. I have tried validating the databases and even copying all tables to a single database - still getting the same results.


** test1.prg
** calling sequence: do test1
clear
close data all
set delete on
open data system
open data data\client1

use data\patient in 0
use data\transact in 0
use source in 0
use carrier in 0

select distinct rtrim(patient.first_name) + ' ' + rtrim(patient.middle_name) + ' ' + patient.last_name as patname, ;
rtrim(patient.address) + ' ' + rtrim(patient.address_2) + ' ' + rtrim(patient.city) + ', ' + patient.state + ' ' + patient.zip_code as paddress, ;
patient.employer, patient.employeradd as addressemp, ;
left(patient.ssn,3) + '-' + subst(patient.ssn,4,2) + '-' + substr(patient.ssn,6) as ssn , ;
rtrim(patient.injury1_date) + iif(empty(patient.injury2_date),'',',' + rtrim(patient.injury2_date)) + ;
iif(empty(patient.injury3_date),'',', ' + rtrim(patient.injury3_date)) + iif(empty(patient.injury4_date),'',', ' + ;
rtrim(patient.injury4_date)) as injurydate, patient.dob, ;
iif(isnull(src.Name_Company),space(30),src.Name_Company) as intakeaa, ;
iif(isnull(src.code),space(60),rtrim(src.address) + ' ' + rtrim(src.city) + ',' + src.state + ' ' + src.zip) as addressaa, ;
iif(isnull(car.Name_company),space(30),car.Name_company) as carriername, ;
iif(isnull(car.code),space(60),rtrim(car.address) + ' ' + rtrim(car.city) + ',' + car.state + ' ' + car.zip) as addresscarr, ;
rtrim(patient.wcab1) + iif(empty(patient.wcab2),'',',' + rtrim(patient.wcab2)) + iif(empty(patient.wcab3),'',', ' +;
rtrim(patient.wcab3)) + iif(empty(patient.wcab4),'',', ' + rtrim(patient.wcab4)) as wcab, ;
sum(tns.current_balance) as balance ;
from client1!patient, client1!recall as rec ;
left join client1!transact as tns on tns.id_patient= patient.id_pati ;
left join system!carrier as car on car.code = patient.code_primary_carrier ;
left join system!source as src on Src.code = patient.intake_aa ;
where patient.id_pati = rec.id_patient and patient.id_pati = 101182

************************************************************
************************************************************

*****!!!!!!!! This Works !!!!!!!!!!*******

** test2.prg
** calling sequence: do test2 with 101182
lparam lnpati
clear
close data all
set delete on
open data system
open data data\client1

select distinct 1 as cursourceid, patient.id_pati, iif(isnull(Source.Name_Company),space(30),Source.Name_Company) as intakeaa, ;
iif(isnull(source.code),space(60),rtrim(source.address) + ' ' + rtrim(source.city) + ',' + source.state + ' ' + source.zip) as addressaa ;
from client1!patient, client1!recall, system!source ;
where patient.id_pati = recall.id_patient and code_recall='LIEN' and Source.code = patient.intake_aa ;
and patient.id_pati = (lnpati) into cursor cursource

select distinct 1 as curcarrierid, patient.id_pati, iif(isnull(Carrier.Name_company),space(30),Carrier.Name_company) as carrier, ;
iif(isnull(Carrier.code),space(60),rtrim(Carrier.address) + ' ' + rtrim(Carrier.city) + ',' + Carrier.state + ' ' + Carrier.zip) as addresscarr ;
from client1!patient, client1!recall, system!carrier ;
where patient.id_pati = recall.id_patient and code_recall='LIEN' and carrier.code = patient.code_primary_carrier;
and patient.id_pati = (lnpati) into cursor curcarrier

select 1 as curTransid, patient.id_pati, sum(transact.current_balance) as balance ;
from client1!patient, client1!recall, client1!transact ;
where patient.id_pati = recall.id_patient and code_recall='LIEN' ;
and transact.id_patient = patient.id_pati;
and patient.id_pati = (lnpati);
group by transact.id_patient ;
into cursor curTrans

select distinct rtrim(patient.first_name) + ' ' + rtrim(patient.middle_name) + ' ' + patient.last_name as patname, ;
rtrim(patient.address) + ' ' + rtrim(patient.address_2) + ' ' + rtrim(patient.city) + ', ' + patient.state + ' ' + patient.zip_code as paddress, ;
patient.employer, patient.employeradd as addressemp, ;
left(patient.ssn,3) + '-' + subst(patient.ssn,4,2) + '-' + substr(patient.ssn,6) as ssn , ;
rtrim(patient.injury1_date) + iif(empty(patient.injury2_date),'',',' + rtrim(patient.injury2_date)) + iif(empty(patient.injury3_date),'',', ' + rtrim(patient.injury3_date)) + iif(empty(patient.injury4_date),'',', ' + rtrim(patient.injury4_date)) as injurydate, patient.dob, ;
rtrim(patient.wcab1) + iif(empty(patient.wcab2),'',',' + rtrim(patient.wcab2)) + iif(empty(patient.wcab3),'',', ' + rtrim(patient.wcab3)) + iif(empty(patient.wcab4),'',', ' + rtrim(patient.wcab4)) as wcab, ;
cursource.intakeaa, cursource.addressaa, curcarrier.carrier, curcarrier.addresscarr, ;
curTrans.balance ;
from client1!patient, client1!recall ;
left outer join cursource on cursourceid = 1;
left outer join curcarrier on curcarrierid = 1;
left outer join curTrans on curTransid = 1;
where patient.id_pati = recall.id_patient and code_recall='LIEN' and patient.id_pati = (lnpati)

use in cursource
use in curcarrier
use in curTrans

return