Re: sql problem

On Mar 23, 10:08 am, Urs Metzger <u..._at_ursmetzger.de> wrote:
> 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.
>
> 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))
>   30  order by key, rn
>   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

got it working. Thanks again !

I restructured some of the sql using 'with' clauses for some of the subqueries so it reads a little easier.

with t1_not_t2 as
(

),
t2_not_t1 as
(

)
(select * from t1_not_t2) a
  full outer join
(select * from t2_not_t1) b
on a.key=b.key and etc.......

thanks again ! Received on Sun Mar 23 2008 - 13:29:31 CDT