topic-18213

Posts: 811

Joined: Tue May 06, 2008 04:28 AM

Record difference of two table
Posted: Sat Feb 27, 2010 02:05 AM

Hi All,

Im using two tables (either by USE command or by SQL). I need to determine the non-existing record from table1 against table2.

also, Im using ADS, is there a way to compare local table from server table ?

Anybody can help?

Thanks,
FAP

Kind Regards,

Frances

Fivewin for xHarbour v18.07

xHarbour v1.2.3.x

BCC 7.3 + PellesC8 ( Resource Compiler only)

ADS 10.1 / MariaDB

Crystal Reports 8.5/9.23 DE

xMate v1.15

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Record difference of two table
Posted: Sat Feb 27, 2010 02:41 AM

With SQL it is very easy to find rows in one table not found in another table ( on the same server ).

I have been out of touch with Advantage SQL. But I can site several constructs used for this purpose.

'MINUS' in Oracle or 'EXCEPT' in MS Sql are ( opposite of UNION ) are used to list the differences.

Code (fw): Select all Collapse

SELECT EmpNo, EmpName
FROM EmployeeRecord
WHERE Salery > 1000
MINUS
SELECT EmpNo, EmpName
FROM EmployeeRecord
WHERE Salery > 2000
ORDER BY EmpName;

If our SQL does not support MINUS or EXCEPT, we can always use 'NOT EXISTS' or 'NOT IN' in any SQL. Also we can use 'OUTER JOIN' and filter for NULL values.

Code (fw): Select all Collapse

SELECT *
FROM suppliers
WHERE not exists (select * from orders Where suppliers.supplier_id = orders.supplier_id);

or

Code (fw): Select all Collapse

SELECT EmpNum, EmpName
FROM EMPOLYEES
WHERE EmpNum NOT IN 
(SELECT EmpNum FROM Table2)

Regards

G. N. Rao.

Hyderabad, India

fraxzi

Posts: 811

Joined: Tue May 06, 2008 04:28 AM

Re: Record difference of two table
Posted: Sat Feb 27, 2010 06:50 AM

Thanks RAO!

Yes on the same server it is easy.

but my problem is, table1 is in server and table2 is in localpc.

I cant execute SQL for two different connection.. am I right?

Regards,
FAP

Kind Regards,

Frances

Fivewin for xHarbour v18.07

xHarbour v1.2.3.x

BCC 7.3 + PellesC8 ( Resource Compiler only)

ADS 10.1 / MariaDB

Crystal Reports 8.5/9.23 DE

xMate v1.15

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Record difference of two table
Posted: Sat Feb 27, 2010 09:49 AM

I cant execute SQL for two different connection.. am I right?

Yes, unless one server has the feature to connect to other server and map it.

Have you tried opening local table and also another table on the server through the same local connection? I am not in touch with ADS now, but probably you can do that.

In any case, for small tables you can compare the two tables programatically.

Regards

G. N. Rao.

Hyderabad, India

fraxzi

Posts: 811

Joined: Tue May 06, 2008 04:28 AM

Re: Record difference of two table
Posted: Wed Mar 03, 2010 08:47 AM

nageswaragunupudi wrote:
..
Have you tried opening local table and also another table on the server through the same local connection? I am not in touch with ADS now, but probably you can do that..
..

Dear RAO,

we cant open table from 2 different connection unless a link is established. but I cant (or at least arc32) do that on server and local connection. I dont know if there's a solution..

Regards,
FAP

Kind Regards,

Frances

Fivewin for xHarbour v18.07

xHarbour v1.2.3.x

BCC 7.3 + PellesC8 ( Resource Compiler only)

ADS 10.1 / MariaDB

Crystal Reports 8.5/9.23 DE

xMate v1.15

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Record difference of two table
Posted: Wed Mar 03, 2010 01:11 PM

Is your requirement one way synchronization of master table on server to replicated table on local pc? If so, I suggest a different solution.

Maintain UpdateTimeStamp column in the Table.
When you want to synchronize,
<psuedo logic>
Step1: Select Max( UpdateTimeStamp ) from local table
Step2: Select * from server table where UpdateTimeStamp > #maxlocaltimestamp#
Step3: Insert new rows and update modified rows in the local table.

Regards

G. N. Rao.

Hyderabad, India

fraxzi

Posts: 811

Joined: Tue May 06, 2008 04:28 AM

Re: Record difference of two table
Posted: Thu Mar 04, 2010 12:45 AM

nageswaragunupudi wrote:Is your requirement one way synchronization of master table on server to replicated table on local pc? If so, I suggest a different solution.

Maintain UpdateTimeStamp column in the Table.
When you want to synchronize,
<psuedo logic>
Step1: Select Max( UpdateTimeStamp ) from local table
Step2: Select * from server table where UpdateTimeStamp > #maxlocaltimestamp#
Step3: Insert new rows and update modified rows in the local table.

Yes. One way sync (update) local table from server.

I'll see what I can do with your great suggestions.

if 'append from' command can determine record not found from source...

like:

Code (fw): Select all Collapse

select( 'localdb' )  

APPEND FROM 'serverdb' where localdb->field_id <> serverdb->field_id     //... similar to this example

Regards,
FAP

Kind Regards,

Frances

Fivewin for xHarbour v18.07

xHarbour v1.2.3.x

BCC 7.3 + PellesC8 ( Resource Compiler only)

ADS 10.1 / MariaDB

Crystal Reports 8.5/9.23 DE

xMate v1.15

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Record difference of two table
Posted: Thu Mar 04, 2010 02:08 AM

Please try my logic. I was using this logic ( and similar logic with rowversions) for oneway sync of tables from servers of different RDMS ( example from Oracle to ADS ). This works satisfactorily

Regards

G. N. Rao.

Hyderabad, India

FiveTech Support Forums

Continue the discussion