Re: sql problem


"Urs Metzger" <urs@xxxxxxxxxxxxx> schreef in bericht
news:fs5rms$3c9$1@xxxxxxxxxxxx
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

Nice work!

Shakespeare


.

Relevant Pages

  • 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" 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
    ... 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)