Re: consolidation of multiple rows
From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 05 Mar 2008 13:38:05 -0800
Message-ID: <1204753083.342535@bubbleator.drizzle.com>
>> 1) If two of the rows in your raw data staging table have different
>> values for the same attribute, say a phone number, which one do you
>> pick or does it matter? If not, then use this skeleton:
>>
>> SELECT company_name, MAX(phone_nbr), MAX(..), ..
>> FROM RawData
>> GROUP BY company_name;
>>
>> else find the conflicts:
>>
>> SELECT company_name AS conflicting_data
>> FROM RawData
>> GROUP BY company_name
>> HAVING COALESCE (NULLIF(COUNT (DISTINCT phone_nbr), 1), NULLIF(COUNT
>> (DISTINCT ..), 1), .. ) IS NOT NULL ;
>>
>> There are a lot of games we can play with a HAVING clause on the raw
>> data. You might want to get a copy of THINKING IN SETS and look at
>> Chapter 10, "Scrubbing Data with Non-1NF Tables" for some more
>> ideas.
Date: Wed, 05 Mar 2008 13:38:05 -0800
Message-ID: <1204753083.342535@bubbleator.drizzle.com>
hpuxrac wrote:
> On Mar 5, 12:49 pm, --CELKO-- <jcelko..._at_earthlink.net> wrote:
>> 1) If two of the rows in your raw data staging table have different
>> values for the same attribute, say a phone number, which one do you
>> pick or does it matter? If not, then use this skeleton:
>>
>> SELECT company_name, MAX(phone_nbr), MAX(..), ..
>> FROM RawData
>> GROUP BY company_name;
>>
>> else find the conflicts:
>>
>> SELECT company_name AS conflicting_data
>> FROM RawData
>> GROUP BY company_name
>> HAVING COALESCE (NULLIF(COUNT (DISTINCT phone_nbr), 1), NULLIF(COUNT
>> (DISTINCT ..), 1), .. ) IS NOT NULL ;
>>
> > Makes sense ... perhaps the OP can give us some more background of > how and why info gets into the raw table. >
>> There are a lot of games we can play with a HAVING clause on the raw
>> data. You might want to get a copy of THINKING IN SETS and look at
>> Chapter 10, "Scrubbing Data with Non-1NF Tables" for some more
>> ideas.
> > What is "Thinking in sets"? and who wrote it ... not familiar with > that title.
Thinking In Sets was written by Joe Celko.
If you know the names Date and Codd you should know who you are addressing: Joe Celko.
-- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Wed Mar 05 2008 - 15:38:05 CST