Re: sql problem
Date: Thu, 20 Mar 2008 17:02:17 +0100
> Ok, I guess I didn't explain very well. Sorry.
> Basically, consider an account/customer. You can have many customers
> per account. That is all I meant by multiple rows per key. Maybe its
> the wrong terminology. Account # is the link field (non-unique) on
> the table and links to an account table elsewhere. What I did not
> explain is that both tables in my example are customer tables, but are
> from two different oracle instances running different types of
> systems. We retrieve the data (in SQL) from table2, in this case,
> over a dblink from a different oracle system. (i.e. select data from
> table1, table2_at_dblink where ....)
> The requirement is I have to come up with a report comparing the two
> customer tables to see if they are in sync. The customer only wants
> on the report those names that are different across the two tables.
> So, what I wanted to do was to get the SQL to return the "like" names
> on the same row so they can be ignored and report the remainder. If I
> can get the "like" names on the same row, I can ignore those through a
> case statement in an outer SQL. So based on my example, you can see
> John is common across both tables for account 1, so it can be ignored,
> only Mary needs to be reported (however, if I can get all names in the
> sql, I can ignore the ones I want, as long as the "like" ones are on
> the same row). The only reason I was trying to get multiple names on
> the same row is so the report can show them side by side, easier to
> Account System 1 System 2
> 1 Susan Jenna
> My first attempt was selecting all the rows from the first table,
> unioning it with the rows from the second table and then doing a full
> outer join, but that did not produce what I was really looking for.
> If there was 1 customer from table 1 and 3 from table 2, I got 3 rows,
> but the customer from table 1 was duplicated on the 2nd and 3rd rows,
> but I need NULLs there since there was not more than 1 record.
> The only other way I can think of doing it is to load up 2 arrays (one
> from each system) and compare the arrays. Was just trying to avoid pl/
> sql based on the number of times this will need to be done (would have
> to be done for each account). I'm open to any other ideas....
> Hope that helps some .....
Maybe not exactly what you asked for, but basically you want only those records that are not in sync. Why you think that it would be better to have them on the same line is beyond me, so here is what I would use:
select t1.key, t1.name,
2 t2.key, t2.name
3 from table1 t1
4 full outer join table2 t2 on (t1.key=t2.key and t1.name=t2.name) 5 where t1.name is null
6* or t2.name is null
KEY NAME KEY NAME ---------- ------------------------------ ---------- ------------------------------ 2 Bob 3 Sheila 2 George 3 Susan 3 Jenna 2 Steve 1 Mary 3 Martha
8 rows selected.
Holger Received on Thu Mar 20 2008 - 11:02:17 CDT