Hi all,
I have a problem about the query on 2 tables, when I start a teiid and query like this:
select crmDB.prospects_scottliu.id,crmDB.prospects_scottliu.phone_mobile,crmDB.prospects_scottliu.first_name,crmDB.prospects_scottliu.date_entered,teiid2602.KC.totalamount
from crmDB.prospects_scottliu JOIN teiid2602.KC ON crmDB.prospects_scottliu.phone_mobile = teiid2602.KC.mobile
where crmDB.prospects_scottliu.id is not null;
----------------------------
the return result seems lose some record which should be retrieved, and the KC table has about 160k records, then I make a second try, I create a new table calld KD which copies the KC's schema, but I only insert 10 recoreds in it, then I query like this:
select crmDB.prospects_scottliu.id,crmDB.prospects_scottliu.phone_mobile,crmDB.prospects_scottliu.first_name,crmDB.prospects_scottliu.date_entered,teiid2602.KD.totalamount
from crmDB.prospects_scottliu JOIN teiid2602.KD ON crmDB.prospects_scottliu.phone_mobile = teiid2602.KD.mobile
where crmDB.prospects_scottliu.id is not null;
this time all results are retrieved.
----------------------------------
and I have the third try, I add some some more conditions and try to retrieve the data which lose in the first attempt, the query like this:
select crmDB.prospects_scottliu.id,crmDB.prospects_scottliu.phone_mobile,crmDB.prospects_scottliu.first_name,crmDB.prospects_scottliu.date_entered,teiid2602.KC.totalamount
from crmDB.prospects_scottliu JOIN teiid2602.KC ON crmDB.prospects_scottliu.phone_mobile = teiid2602.KC.mobile
where crmDB.prospects_scottliu.id is not null and crmDB.prospects_scottliu.phone_mobile in ('1111','2222','3333','4444') ;
and this time all the records are retrieved, so I wonder if there is anything in teiid which limit the table when we try the query on 2 tables, if it is so, is anyone know how can I fix this problem, thanks.
