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