Re: sql problem
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Sun, 23 Mar 2008 17:57:03 +0100
Message-ID: <47e68be8$0$14347$e4fe514c@news.xs4all.nl>
> 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.
> 31 /
> hth,
> Urs Metzger
Date: Sun, 23 Mar 2008 17:57:03 +0100
Message-ID: <47e68be8$0$14347$e4fe514c@news.xs4all.nl>
"Urs Metzger" <urs_at_ursmetzger.de> schreef in bericht
news:fs5rms$3c9$1_at_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.
>> 30 order by key, rn
> 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))
> 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 Received on Sun Mar 23 2008 - 11:57:03 CDT