Re: sql problem
From: Urs Metzger <urs_at_ursmetzger.de>
Date: Sat, 22 Mar 2008 12:39:14 +0100
Message-ID: <fs2r38$gdq$1@online.de>
>> "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 -
25 /
Date: Sat, 22 Mar 2008 12:39:14 +0100
Message-ID: <fs2r38$gdq$1@online.de>
marfi95 schrieb:
> 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. >
Here we go!
SQL> select key, Table1Val, TableBVal from
2 (select nvl(a.key, b.key) as Key, a.name as Table1Val, 3 b.name as TableBVal, nvl(a.r, b.r) as rn 4 from (select key, name, 5 row_number() over(partition by key order by name) as r 6 from (select key, name 7 from table1 8 minus 9 select key, name 10 from table2)) a 11 full outer join 12 (select key, name, 13 row_number() over(partition by key order by name) as r 14 from (select key, name 15 from table2 16 minus 17 select key, name 18 from table1)) b 19 on a.key = b.key and a.r = b.r 20 union 21 select a.key, a.name, b.name, 0 22 from table1 a, table2 b 23 where a.key = b.key and a.name = b.name)24 order by key, rn
25 /
KEY TABLE1VAL TABLEBVAL
---------- ------------------------------ ------------------------------
1 John John
1 Mary
2 Mark Mark
2 Bob Steve
2 George
3 Sheila Jenna
3 Susan Martha
hth,
Urs Metzger
Received on Sat Mar 22 2008 - 06:39:14 CDT