[RBASE-L] - Re: compare rows in 2 tables

[MDRD]

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" <dmcgr...@qmiusa.com>
Sent: Thursday, February 11, 2010 12:14 PM
To: "RBASE-L Mailing List" <rbase-l@rbase.com>
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