Re: sql problem

On Mar 20, 10:46 am, patrick <pgov..._at_u.washington.edu> wrote:
> On Mar 20, 7:29 am, marfi95 <marf..._at_yahoo.com> wrote:
>
>
>
>
>
> > On Mar 20, 5:04 am, "Shakespeare" <what..._at_xs4all.nl> wrote:
>
> > > "marfi95" <marf..._at_yahoo.com> schreef in berichtnews:0aff1652-033d-4cf9-af1a-0bc346802474_at_m3g2000hsc.googlegroups.com...
>
> > > >I am running into a problem at work that I have not come up with a
> > > > good solution for yet (w/o using pl/sql a record at time).  First off,
> > > > this is 10g.  What I have is 2 keyed tables that can have a different
> > > > number of rows per key.  The particular application here is basically
> > > > an account/customer system.  So, for example, Table1  could have 2
> > > > rows per key, while Table2 could have 3 rows per key.  What I need to
> > > > do is pivot the data to a row, but the catch here is that I only want
> > > > it pivoted to a max of 2 columns.  I have used the standard pivot
> > > > stuff with
> > > > select key, max(decode(.....)....., but it requires a group by clause,
> > > > which then pivots to 1 row.  I need to be able to pivot to multiple
> > > > rows.  If you dont group by (and thus no max(decode), you get pivoted
> > > > data, but a different column on each row.   I've also tried using full
> > > > outer join, but in that case, I got the data pivoted, but if table 1
> > > > only had 1 row and table 2 had 2 rows, I got back 2 rows with the data
> > > > from the first row duplicated on the second row.  The 2 tables have
> > > > basically the same structure.
>
> > > > The other requirement I have is that if there is a common name in the
> > > > name field, I need that displayed on the same row.  For example, if
> > > > table1 had key1, John and table2 had key1, rows Mary and John, I need
> > > > the John rows together, followed by a Mary row.
>
> > > > I've included some scripts to put a very simplified sample out there.
> > > > Also, have what each table looks like and what I'm trying to get to.
> > > > Any ideas would be great.
>
> > > > create table table1
> > > > (
> > > >    key    number(),
> > > >    name   varchar2(30),
> > > > );
>
> > > > create table table2
> > > > (
> > > >    key    number(),
> > > >    name   varchar2(30),
> > > > );
>
> > > > insert into table1 (1, 'John')
> > > > insert into table2 (1, 'Mary')
> > > > insert into table2 (1, 'John')
> > > > insert into table1 (2, 'Bob')
> > > > insert into table1 (2, 'Mark')
> > > > insert into table1 (2,'George')
> > > > insert into table2 (2,'Mark')
> > > > insert into table2 (2,'Steve')
> > > > insert into table1 (3,'Susan')
> > > > insert into table1 (3,'Sheila')
> > > > insert into table2 (3,'Jenna')
> > > > insert into table2 (3,'Martha')
>
> > > > What I need is the following:
>
> > > > Key             Table1Val              Table2Val
> > > > -----------        --------------              --------------
> > > > 1                 John                      John
> > > > 1                                              Mary
> > > > 2                 Bob
> > > > 2                 Mark                      Mark
> > > > 2                 George
> > > > Steve
> > > > 3                 Susan                    Jenna
> > > > 3                 Sheila                    Martha
>
> > > > So basically need to be joined by the name so they end up on same
> > > > row.  Doesn't matter what order the names are in, as long as the ones
> > > > that are the same are on the same row.  So the Bob and George row
> > > > could be reversed here (nor does it matter if Bob ends up in the same
> > > > row as Steve and George was on the row by itself)
>
> > > > Hope this makes some sense.
>
> > > > Many thanks !
>
> > > Does not make any sense to me, what business requirement is this? What user
> > > would ask for this?
>
> > > Shakespeare
>
> > > Shakespeare- Hide quoted text -
>
> > > - Show quoted text -
>
> > 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 .....- Hide quoted text -
>
> > - Show quoted text -
>
> Why not just
> select 'table1', key, name
>   from table1
>  where not exists (select 'x' from table2
>                     where key = key
>                       and name = name)
> union
> select 'table2', key, name
>   from table2
>  where not exists (select 'x' from table1
>                     where key = key
>                       and name = name)
> order by 2,1
>
> ====>Patrick- Hide quoted text -
>
> - Show quoted text -

That works fine for the data itelf, but it comes out vertically, but what I really prefer is the data pivoted as such:

> KEY NAME KEY NAME
> ---------- ------------------------------ ---------- ------------------------------

             1                                      1  Mary


>           2 Bob                               2  Steve

>           2 George

>           3 Sheila                            3  Jenna

>           3 Susan                            3  Martha

Thanks for the reply ! Received on Thu Mar 20 2008 - 13:34:45 CDT