Re: sql problem

marfi95 schrieb:
On Mar 22, 7:17 pm, marfi95 <marf...@xxxxxxxxx> wrote:
On Mar 22, 4:28 pm, marfi95 <marf...@xxxxxxxxx> wrote:





On Mar 22, 6:39 am, Urs Metzger <u...@xxxxxxxxxxxxx> wrote:
marfi95 schrieb:
On Mar 20, 4:35 pm, "Shakespeare" <what...@xxxxxxxxx> wrote:
"marfi95" <marf...@xxxxxxxxx> schreef in berichtnews: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- Hide quoted text -
- Show quoted text -
I like that idea !
You got it though; my app dba is discouraging me from using pl/sql,
although I told them to come up with a better solution (which they
haven't yet).
Some things straight SQL is just not meant to do.
Here we go!
SQL> select key, Table1Val, TableBVal from
2 (select nvl(a.key, b.key) as Key, a.name as Table1Val,
3 b.name as TableBVal, nvl(a.r, b.r) as rn
4 from (select key, name,
5 row_number() over(partition by key order by name) as r
6 from (select key, name
7 from table1
8 minus
9 select key, name
10 from table2)) a
11 full outer join
12 (select key, name,
13 row_number() over(partition by key order by name) as r
14 from (select key, name
15 from table2
16 minus
17 select key, name
18 from table1)) b
19 on a.key = b.key and a.r = b.r
20 union
21 select a.key, a.name, b.name, 0
22 from table1 a, table2 b
23 where a.key = b.key and a.name = b.name)
24 order by key, rn
25 /
KEY TABLE1VAL TABLEBVAL
---------- ------------------------------ ------------------------------
1 John John
1 Mary
2 Mark Mark
2 Bob Steve
2 George
3 Sheila Jenna
3 Susan Martha
hth,
Urs Metzger- Hide quoted text -
- Show quoted text -
this is awesome, thanks, I will give it a shot.- Hide quoted text -
- Show quoted text -
Urs, that works well with an exception. I didn't realize that the
data from 1 system was all uppercase, while the other could be mixed
case. However, on my output, I need to display the original case, but
not report like entries (MARK = mark). If I use upper on everything in
the two queries for the full outer join, the output is upper on both
sides (table1val and table2val). I need to return the original case
from the system, but use upper in the comparison, but the 'minus' is
causing me issues since I can't return the real value. At least I
haven't figured out how yet.

All the rest is excellent !!!- Hide quoted text -

- Show quoted text -

the portion where the union is happening is ok. I just selected name
where upper(....)=upper(...)- gives me the original case. So the
items that are in both are ok, its the ones that are not where the
issue happens. The section that does the "minus" is where I'm having
the problem.

Thanks !

Hi marfi95,

strange name you have! Anyway, i would have perferred to replace

select key, name
from table1
minus
select key, name
from table2

with

select key, name
from table1
where (key, upper(name)) not in (
select key, upper(name)
from table2)

but that yields an ORA-03113 on my Oracle 10g XE database.
However, if you have a real, grown-up database, you should definitively try that.

On my system. I had to do this (and I have to admit it hurts):

SQL> update table2 set name = upper(name);

6 Zeilen wurden aktualisiert.

SQL> commit;

Transaktion mit COMMIT abgeschlossen.

SQL> select key, Table1Val, TableBVal from
2 (select nvl(a.key, b.key) as Key, a.name as Table1Val,
3 b.name as TableBVal, nvl(a.r, b.r) as rn
4 from (select key, (select name
5 from table1 t
6 where t.key = ia.key
7 and upper(t.name) = ia.uname) as name,
8 row_number() over(partition by key order by uname) as r
9 from (select key, upper(name) as uname
10 from table1
11 minus
12 select key, upper(name)
13 from table2) ia) a
14 full outer join
15 (select key, (select name
16 from table2 t
17 where t.key = ib.key
18 and upper(t.name) = ib.uname) as name,
19 row_number() over(partition by key order by uname) as r
20 from (select key, upper(name) as uname
21 from table2
22 minus
23 select key, upper(name)
24 from table1) ib) b
25 on a.key = b.key and a.r = b.r
26 union
27 select a.key, a.name, b.name, 0
28 from table1 a, table2 b
29 where a.key = b.key and upper(a.name) = upper(b.name))
30 order by key, rn
31 /

KEY TABLE1VAL TABLEBVAL
---------- ------------------------------ ------------------------------
1 John JOHN
1 MARY
2 Mark MARK
2 Bob STEVE
2 George
3 Sheila JENNA
3 Susan MARTHA


hth,
Urs Metzger
.

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 ... table1, table2@dblink where ....) ... customer tables to see if they are in sync. ... what I wanted to do was to get the SQL to return the "like" ...
    (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
    ... 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)