Re: sql problem

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.
Received on Sat Mar 22 2008 - 16:28:11 CDT