comparing tables

My employer has asked me to compare two databases which are not connected (1 in Oracle 7, and 1 in oracle 8). but with more or less the same set of tables and records (let's say: custumors and addresses). Can anyone give me a hint how to compare those tables. I need to know which records in one table are missing in the other one? I need to know which records have the same unique identification but different other attributes, etc.First, you need to establish a database link from one database to the other, so that you can connect to one database and select data from the other, e.g.

DB1> SELECT * FROM scott.emp@.DB2;

Your DBA should be able to help with this.

Then you can compare records. To see which records in DB1 are missing in DB2, run this in DB1:

SELECT keycol1, keycol2, ... FROM table1
MINUS
SELECT keycol1, keycol2, ... FROM table1@.DB2;

To find records where keys match but other attributes differ:

SELECT t1.keycol1, t1.keycol2, ...
FROM table t1, table@.DB2 t2
WHERE t1.keycol1 = t2.keycol1
AND t1.keycol2 = t2.keycol2
...
AND ( t1.attribute1 != t2.attribute1
OR t1..attribute2 != t2.attribute2
OR ...
);

BEWARE OF NULLS!!!

If attribute1 can be NULL, then you must allow for that. The correct way is:

NOT ( ( t1.attribute1 IS NULL AND t2.attribute1 IS NULL)
OR t1.attribute1 = t2.attribute1
)

A simpler way is:

NVL(t1.attribute1,'?') = NVL(t2.attribute1,'?')

... but you must be sure to pick a suitable value (e.g. '?') that is (a) valid for the datatype of attribute1 and (b) not a value that you will actually find in attribute1.

comparing facts with different granularity

Hi,
I have a sale fact connected to a few dimensions like time(month),
product, customer, area.
I have also a budget fact connected to time(year), customer_group.
Customer_group is an itermediate level in customer hierarchy (Sector,
Segment, Customer_Group, Customer).
I was thinking of building a virtual cube, but I don't know how to
connect the two facts at differents level dimensions in dimensions
time and customer.
Can anybody help me?
Thank you
Roberto BottoYou can solve this through disabled property.
First of all, you normalize the time dimension table according to the sale
and budget fact tables.
In your budegt cube, set the disabled property of the levels, below year
level of time dimension, to Yes.
Do the same steps for customer dimension.
Create a virtual cube based on sale and budget cubes.
Ohjoo Kwon
"Roberto Botto" <orion48@.aruba.it> wrote in message
news:a6c31b2d.0503240516.2342ee7@.posting.google.com...
> Hi,
> I have a sale fact connected to a few dimensions like time(month),
> product, customer, area.
> I have also a budget fact connected to time(year), customer_group.
> Customer_group is an itermediate level in customer hierarchy (Sector,
> Segment, Customer_Group, Customer).
> I was thinking of building a virtual cube, but I don't know how to
> connect the two facts at differents level dimensions in dimensions
> time and customer.
> Can anybody help me?
> Thank you
> Roberto Botto|||Thank you for the suggestion, but I can't make it work.
The problem is in the join between the customer dimension and the budget
fact.
The budget fact foreign key is not the customer, which is primay key in
the customer dimension, but an higher level code (the customer group).
Hence the join between the fact budget and the customer dimension,
through the customer group, multiplies the the budget records. This is
my problem, not just hiding the lower dimension levels.
Thank you
Roberto Botto
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Why don't you normalize the customer dimension table according to the sales
and budget fact tables.
For example, Customer_Group(Sector, Segment, Customer_Group) and
Customer(Customer).
Ohjoo
"Roberto Botto" <orion48@.aruba.it> wrote in message
news:O$XMioRMFHA.580@.TK2MSFTNGP15.phx.gbl...
> Thank you for the suggestion, but I can't make it work.
> The problem is in the join between the customer dimension and the budget
> fact.
> The budget fact foreign key is not the customer, which is primay key in
> the customer dimension, but an higher level code (the customer group).
> Hence the join between the fact budget and the customer dimension,
> through the customer group, multiplies the the budget records. This is
> my problem, not just hiding the lower dimension levels.
> Thank you
>
> Roberto Botto
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!sqlsql

communication btwn website abd emulator

hi there,

I have developed a web base and emulator using VS 2005.the web site is connected to SQL server database 2005 and the emulator (smart device) is connected to the SQL Mobile Server. I have published, web sync and subscribe the SQL Server database to the SQL Server Mobile. My problem now is to display any immediate chages that take place in the SQL Server database on the emulator. How do I do that.. PLSSSS HELP ME....

regards,

Dilashani

Moving to SQL Server Compact Edition forum where it has got better chances of being answered.

-Thanks,

Mohit