Re: sql problem

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 ..... Received on Thu Mar 20 2008 - 09:29:52 CDT