Re: sql problem

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. Received on Thu Mar 20 2008 - 16:50:49 CDT