Re: sql problem
- From: marfi95 <marfi95@xxxxxxxxx>
- Date: Thu, 20 Mar 2008 07:29:52 -0700 (PDT)
On Mar 20, 5:04 am, "Shakespeare" <what...@xxxxxxxxx> wrote:
"marfi95" <marf...@xxxxxxxxx> schreef in berichtnews:0aff1652-033d-4cf9-af1a-0bc346802474@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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@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 .....
.
- Follow-Ups:
- Re: sql problem
- From: Holger Baer
- Re: sql problem
- From: patrick
- Re: sql problem
- References:
- sql problem
- From: marfi95
- Re: sql problem
- From: Shakespeare
- sql problem
- Prev by Date: Re: testing memory parameters
- Next by Date: Re: Unsupported Recovery Technique......?
- Previous by thread: Re: sql problem
- Next by thread: Re: sql problem
- Index(es):
Relevant Pages
|
