Ironically, I think that was addressed from Bill D this morning.
"You would rarely opt to avoid using an index. The rare circumstance would be a query with many conditions in the WHERE clause, and where R:BASE's query optimizer was not automatically picking the best index. You can force it to go on to what it would consider the second-best index by putting the first value (not the whole condition, but the value) in parentheses." It's only really fresh to me because I had to read it twice. However you could just index that table prior to? Sincerely, Paul D. -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of MDRD Sent: Thursday, February 11, 2010 1:32 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: compare rows in 2 tables Thanks Dennis I never knew you could do that. I think the problem is a bad index making it look like there is 116 rows of data doing a Select Count when in fact there is only 115 rows. Is there a way to do select count(*) from tran_daily where ........ that would not use the Index and show 115 rows? What I am thinking is do 2 select count(*) from tran_daily ..... and if those numbers do not match Pack Index, Thanks Marc -------------------------------------------------- From: "Dennis McGrath" <[email protected]> Sent: Thursday, February 11, 2010 12:14 PM To: "RBASE-L Mailing List" <[email protected]> Subject: [RBASE-L] - Re: compare rows in 2 tables > That statement takes way too much work for the engine on a large dataset. > This would be better, and way faster. Custnum should be indexed in > tran_hist. > > Set eqnull on > > edit all from tran_Daily T1 + > WHERE FAILS (SELECT Custnum FROM tran_hist + > WHERE Custnum = T1.custnum + > AND date_con = T1.date_con + > AND tr_date = T1.tr_date + > AND ch_code = T1.ch_code + > AND ch_price = T1.ch_price + > AND ptest = T1.ptest + > AND cknum = T1.cknum + > AND date_frm = T1.date_frm + > AND date_to = T1.date_to > AND treat_dr = T1.treat_dr > AND inshold = T1.insold ) > > > Dennis McGRath > ---------------------------------------------------------------------------- ------------------------ > This is what I am using but it finds no rows, yet my calculations show we > are missing some rows. > > edit all from tran_Daily + > WHERE ( CTXT(custnum) + CTXT(date_con) + + > CTXT(tr_date) + ch_code + CTXT(ch_price) + + > CTXT(ptest) + cknum + CTXT(date_frm) + + > CTXT(date_to) + treat_dr + inshold ) NOT IN + > (SELECT ( CTXT(custnum) + CTXT(date_con) + + > CTXT(tr_date) + ch_code + CTXT(ch_price) + + > CTXT(ptest) + cknum + CTXT(date_frm) + + > CTXT(date_to) + treat_dr + inshold) FROM tran_hist) > > Thanks for any suggestions > Marc > > >
