Re: sql problem
From: Urs Metzger <urs_at_ursmetzger.de>
Date: Sun, 23 Mar 2008 16:08:08 +0100
Message-ID: <fs5rms$3c9$1@online.de>
>> On Mar 22, 4:28 pm, marfi95 <marf..._at_yahoo.com> wrote:
>>
>>
>>
>>
>>
>>> On Mar 22, 6:39 am, Urs Metzger <u..._at_ursmetzger.de> wrote:
>>>> marfi95 schrieb:
>>>>> On Mar 20, 4:35 pm, "Shakespeare" <what..._at_xs4all.nl> wrote:
>>>>>> "marfi95" <marf..._at_yahoo.com> schreef in berichtnews:c42355c8-51bc-477e-8d43-fd7445a7b955_at_m44g2000hsc.googlegroups.com...
>>>>>> On Mar 20, 11:02 am, Holger Baer <baer_at_dont_ask.com> 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_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
>>>>>>>> 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 -
31 /
Date: Sun, 23 Mar 2008 16:08:08 +0100
Message-ID: <fs5rms$3c9$1@online.de>
marfi95 schrieb:
> On Mar 22, 7:17 pm, marfi95 <marf..._at_yahoo.com> wrote:
>> On Mar 22, 4:28 pm, marfi95 <marf..._at_yahoo.com> wrote:
>>
>>
>>
>>
>>
>>> On Mar 22, 6:39 am, Urs Metzger <u..._at_ursmetzger.de> wrote:
>>>> marfi95 schrieb:
>>>>> On Mar 20, 4:35 pm, "Shakespeare" <what..._at_xs4all.nl> wrote:
>>>>>> "marfi95" <marf..._at_yahoo.com> schreef in berichtnews:c42355c8-51bc-477e-8d43-fd7445a7b955_at_m44g2000hsc.googlegroups.com...
>>>>>> On Mar 20, 11:02 am, Holger Baer <baer_at_dont_ask.com> 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_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
>>>>>>>> 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
Received on Sun Mar 23 2008 - 10:08:08 CDT