Re: sql problem

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 !!! Received on Sat Mar 22 2008 - 19:17:23 CDT