Re: sql problem

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