Re: sql problem


"marfi95" <marfi95@xxxxxxxxx> schreef in bericht
news:c42355c8-51bc-477e-8d43-fd7445a7b955@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Mar 20, 11:02 am, Holger Baer <baer@xxxxxxxxxxxx> wrote:
marfi95 wrote:

[Snip]







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@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
read.

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
SQL> /

KEY NAME KEY NAME
---------- ------------------------------ ---------- ------------------------------
2 Bob
3 Sheila
2 George
3 Susan
3 Jenna
2 Steve
1 Mary
3 Martha

8 rows selected.

HTH
Holger- Hide quoted text -

- Show quoted text -

Thanks for the reply. This is where I got stuck on my original try
with the full outer join. I only had included the "like" ones because
I thought it might be easier, but they don't have to be there.

Is there any way to take what you have done and have the "like' key
rows be the same row. Like this:

KEY NAME KEY NAME
---------- ------------------------------ ---------- ------------------------------
1 1 Mary
2 Bob 2 Steve
2 George
3 Sheila 3 Jenna
3 Susan 3 Martha

Thanks again !



Yes: print your list of names and codes, take a pair of scissors and some
glue... <g>

I would definitely use some pl/sql here (but I know some DBA's don't want to
use pl/sql...);
write a procedure that outputs max. 2 entries per line , and if the code
changes, start on a new line.

Shakespeare




.

Relevant Pages

  • Re: sql problem
    ... Account # is the link field on ... The requirement is I have to come up with a report comparing the two ... customer tables to see if they are in sync. ... what I wanted to do was to get the SQL to return the "like" names ...
    (comp.databases.oracle.server)
  • Re: sql problem
    ... Account # is the link field on ... The requirement is I have to come up with a report comparing the two ... customer tables to see if they are in sync. ... what I wanted to do was to get the SQL to return the "like" names ...
    (comp.databases.oracle.server)
  • Re: sql problem
    ... Account # is the link field on ... The requirement is I have to come up with a report comparing the two ... customer tables to see if they are in sync. ... what I wanted to do was to get the SQL to return the "like" names ...
    (comp.databases.oracle.server)
  • Re: sql problem
    ... the table and links to an account table elsewhere. ... customer tables to see if they are in sync. ... on the report those names that are different across the two tables. ... what I wanted to do was to get the SQL to return the "like" names ...
    (comp.databases.oracle.server)
  • Re: sql problem
    ... Account # is the link field on ... customer tables to see if they are in sync. ... on the report those names that are different across the two tables. ... what I wanted to do was to get the SQL to return the "like" names ...
    (comp.databases.oracle.server)