Re: sql problem
Date: Thu, 20 Mar 2008 08:46:15 -0700 (PDT)
Message-ID: <20efc0f2-1475-45ea-85c4-003feed9232b@e6g2000prf.googlegroups.com>
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 Received on Thu Mar 20 2008 - 10:46:15 CDT