Reconcilliation

Reconcilliation

Post by Peter Newma » Wed, 03 Mar 2004 21:26:06



I have severay tables all linked by a Client_Ref Field. Im need to find out if any of the tables are missing records for any of the clients

TAdmin
Client_Ref  VARCHAR(6)
CompanyName VARCHAR(25)

TComms
Client_Ref  VARCHAR(6)

TAccounts
Client_Ref  VARCHAR(6)

TFees
Client_Ref  VARCHAR(6)

TUsers
Client_Ref  VARCHAR(6)

Im trying to get a result to show any clients that do NOT have a record in all tables

Client_Ref       CompanyName       TAdmin     TComms      TAccounts      TFees     TUsers
121212           Test Company 1          YES           NO              YES             YES         NO
225225           Test Company 2           NO           NO               NO              YES         NO

Can anybody help?

 
 
 

Reconcilliation

Post by Uri Diman » Wed, 03 Mar 2004 21:36:20


Peter
What is about posting sample data?


Quote:> I have severay tables all linked by a Client_Ref Field. Im need to find

out if any of the tables are missing records for any of the clients
Quote:

> TAdmin
> Client_Ref  VARCHAR(6)
> CompanyName VARCHAR(25)

> TComms
> Client_Ref  VARCHAR(6)

> TAccounts
> Client_Ref  VARCHAR(6)

> TFees
> Client_Ref  VARCHAR(6)

> TUsers
> Client_Ref  VARCHAR(6)

> Im trying to get a result to show any clients that do NOT have a record in
all tables

> Client_Ref       CompanyName       TAdmin     TComms      TAccounts
TFees     TUsers
> 121212           Test Company 1          YES           NO              YES
YES         NO
> 225225           Test Company 2           NO           NO               NO
YES         NO

> Can anybody help?


 
 
 

Reconcilliation

Post by Roji. P. Thoma » Wed, 03 Mar 2004 21:44:50


Assuming that your client table has one to one realtionship with other
tables doing a left outer join will give u the result

here is an example from pubs.

SELECT j.job_id,
CASE WHEN e.job_Id IS NULL THEN 'NO' ELSe 'YES' END
FROM jobs j
LEFT OUTER JOIN employee e
on j.job_Id = e.job_id

--
Roji. P. Thomas
SQL Server Programmer

Quote:> I have severay tables all linked by a Client_Ref Field. Im need to find

out if any of the tables are missing records for any of the clients
Quote:

> TAdmin
> Client_Ref  VARCHAR(6)
> CompanyName VARCHAR(25)

> TComms
> Client_Ref  VARCHAR(6)

> TAccounts
> Client_Ref  VARCHAR(6)

> TFees
> Client_Ref  VARCHAR(6)

> TUsers
> Client_Ref  VARCHAR(6)

> Im trying to get a result to show any clients that do NOT have a record in
all tables

> Client_Ref       CompanyName       TAdmin     TComms      TAccounts
TFees     TUsers
> 121212           Test Company 1          YES           NO              YES
YES         NO
> 225225           Test Company 2           NO           NO               NO
YES         NO

> Can anybody help?

 
 
 

Reconcilliation

Post by Vishal Parka » Wed, 03 Mar 2004 21:53:46


Try:

select client_ref , companyname,
case when (select max(client_ref) from
 tcomms where client_ref = a.client_ref) is not null then 'Y' else 'N' end tcomms ,
case when (select max(client_ref) from
 taccounts where client_ref = a.client_ref) is not null then 'Y' else 'N' end taccounts,
case when (select max(client_ref) from
 tfees where client_ref = a.client_ref) is not null then 'Y' else 'N' end tfees,
case when (select max(client_ref) from
 tusers where client_ref = a.client_ref) is not null then 'Y' else 'N' end tusers
from tadmin a

--
Vishal Parkar

 
 
 

Reconcilliation

Post by Peter Newma » Wed, 03 Mar 2004 22:16:09


sorry Uri.  

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TAccounts]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TAccounts]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TComms]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TComms]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TUsers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TUsers]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Tadmin]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Tadmin]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Tfees]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Tfees]
GO
CREATE TABLE [dbo].[TAccounts] (
        [Client_Ref] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TComms] (
        [Client_Ref] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TUsers] (
        [Client-Ref] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Tadmin] (
        [Client_ref] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [Company] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Tfees] (
        [Client_Ref] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

SET NOCOUNT ON
INSERT INTO [Tadmin] ([Client_ref],[Company])VALUES('121212','Test Company 1')
INSERT INTO [Tadmin] ([Client_ref],[Company])VALUES('225225','Test Company 2')
SET NOCOUNT OFF

SET NOCOUNT ON
INSERT INTO [TFees] ([Client_Ref])VALUES('121212')
INSERT INTO [TFees] ([Client_Ref])VALUES('225225')
SET NOCOUNT OFF

SET NOCOUNT ON
INSERT INTO [TAccounts] ([Client_Ref])VALUES('121212')
SET NOCOUNT OFF

Should produce
Client_Ref       CompanyName       TAdmin     TComms      TAccounts      TFees     TUsers
121212           Test Company 1          YES           NO              YES             YES         NO
225225           Test Company 2          YES           NO               NO              YES         NO

 
 
 

Reconcilliation

Post by Vishal Parka » Wed, 03 Mar 2004 22:26:16


hi peter,
in your  "tusers" table "client_ref" column is spelled as "client-ref" that is why the query
i've posted earlier will give you error. you can try following.

select client_ref , company, 'Y' tadmin,
case when (select max(client_ref) from
 tcomms where client_ref = a.client_ref) is not null then 'Y' else 'N' end tcomms ,
case when (select max(client_ref) from
 taccounts where client_ref = a.client_ref) is not null then 'Y' else 'N' end taccounts,
case when (select max(client_ref) from
 tfees where client_ref = a.client_ref) is not null then 'Y' else 'N' end tfees,
case when (select max([client-ref]) from
 tusers where [client-ref] = a.client_ref) is not null then 'Y' else 'N' end tusers
from tadmin a

Or the tables tcomms,taccounts, tfees and tusers contains one row for each client_ref then you
can remove MAX() aggregate function as well.

--
Vishal Parkar

 
 
 

Reconcilliation

Post by Uri Diman » Wed, 03 Mar 2004 22:34:18


Thank ,Peter
I see you have got one solution from Vishal
Also, look at this one
SELECT
  Client_Ref,
  CASE
    WHEN EXISTS (
      SELECT * FROM Tadmin r
      WHERE  r.Client_ref = h.Client_ref)
    THEN 1
    ELSE 0
  END AS TAdmin,
  CASE
    WHEN EXISTS (
      SELECT * FROM TComms r
      WHERE  r.Client_Ref = h.Client_Ref)
    THEN 1
    ELSE 0
  END AS TComms,
  CASE
    WHEN EXISTS (
      SELECT * FROM TAccounts r
      WHERE  r.Client_Ref = h.Client_Ref)
    THEN 1
    ELSE 0
  END AS TAccounts,
  CASE
    WHEN EXISTS (
      SELECT * FROM TFees r
      WHERE  r.Client_Ref = h.Client_Ref)
    THEN 1
    ELSE 0
  END AS TFees,
  CASE
    WHEN EXISTS (
      SELECT * FROM TUsers r
      WHERE  r.[Client-Ref] = h.Client_Ref)
    THEN 1
    ELSE 0
  END AS TUsers
FROM Tadmin h


Quote:> sorry Uri.

> if exists (select * from dbo.sysobjects where id =

object_id(N'[dbo].[TAccounts]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Quote:> drop table [dbo].[TAccounts]
> GO
> if exists (select * from dbo.sysobjects where id =

object_id(N'[dbo].[TComms]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Quote:> drop table [dbo].[TComms]
> GO
> if exists (select * from dbo.sysobjects where id =

object_id(N'[dbo].[TUsers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Quote:> drop table [dbo].[TUsers]
> GO
> if exists (select * from dbo.sysobjects where id =

object_id(N'[dbo].[Tadmin]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Quote:> drop table [dbo].[Tadmin]
> GO
> if exists (select * from dbo.sysobjects where id =

object_id(N'[dbo].[Tfees]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Quote:> drop table [dbo].[Tfees]
> GO
> CREATE TABLE [dbo].[TAccounts] (
> [Client_Ref] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[TComms] (
> [Client_Ref] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[TUsers] (
> [Client-Ref] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Tadmin] (
> [Client_ref] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Company] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Tfees] (
> [Client_Ref] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO

> SET NOCOUNT ON
> INSERT INTO [Tadmin] ([Client_ref],[Company])VALUES('121212','Test Company
1')
> INSERT INTO [Tadmin] ([Client_ref],[Company])VALUES('225225','Test Company
2')
> SET NOCOUNT OFF

> SET NOCOUNT ON
> INSERT INTO [TFees] ([Client_Ref])VALUES('121212')
> INSERT INTO [TFees] ([Client_Ref])VALUES('225225')
> SET NOCOUNT OFF

> SET NOCOUNT ON
> INSERT INTO [TAccounts] ([Client_Ref])VALUES('121212')
> SET NOCOUNT OFF

> Should produce
> Client_Ref       CompanyName       TAdmin     TComms      TAccounts
TFees     TUsers
> 121212           Test Company 1          YES           NO              YES
YES         NO
> 225225           Test Company 2          YES           NO               NO

YES         NO
 
 
 

1. ADO reconcilliation of "offline" datasets

Hello,

I am using ADO (under Delphi 6) to implement a "briefcase" model of my
simple
database. When connected to the network, the server is used as the source of
the data and an option allows you to "go offline" where it uses the
savetofile ability of the ADO Dataset. When the application is started it
checks if an "off line" dataset is available and uses that allowing you to
add, delete and modify records and save the change log along with the data
back to disk. When you are connected to a server, the Change log can be
applied to the server using the updatebatch method of the ADO dataset. All
pretty standard stuff.

Now, insertions, deletions and modifications work when there is only one
"off line" dataset. If I have two (perhaps on two separate machines) I need
to "merge" the two change logs. The question is....

How do you do this in ADO? I cannot find a way to trap reconciliation
errors. Do I
have to go through each record checking its status and  handling them
individually? How do other people handle this.

Many thanks for help and pointers to help or examples

Stuart

2. JDBC and firewall