RE: Duplicate records on insert

No, because I don't have price code on the source code file.  
I am trying to fetch it from the price file.  

Although I think the point is moot now.  Even though the 3rd party
software didn't bother to enforce referential integrity at the file
level, they do enforce it in their programs.  It was one of our in-house
programs which created the duplicate records.  And even that has been
fixed, but whoever fixed it neglected to correct the bad data that was
out there.

So really all I'll have to do is correct the bad data, and I should be
OK. 

But it was an educational exercise anyhow.

Thanks for everyone's input. 

Greg

>-----Original Message-----
>From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
>bounces@xxxxxxxxxxxx] On Behalf Of Hauser, Birgitta
>Sent: Tuesday, September 13, 2005 11:25 AM
>To: 'Midrange Systems Technical Discussion'
>Subject: AW: Duplicate records on insert
>
>Hi,
>
>am I wrong, or shouldn't you also join with the price?
>
>Insert into OutFile
>Select
>  Company,
>  SourceCode,
>  OtherFields,
>  PriceCode,
>>From SourceFile a inner join
>     (select company, sourcecode, min(pricecode) as MinPrice
>      from PriceFile
>      group by company, sourcecode) b
>     On     a.Company   = b.Company and a.SourceCode = b.SourceCode
>        and a.pricecode = MinPrice
>
>Birgitta
>
>-----Original Message-----
>Subject: Duplicate records on insert
>
>I'm going to be using Embedded SQL to populate one of our Data
Warehouse
>files.  I've been working on the SQL statement interactively to make
>sure it works, and encountered a problem.
>
>
>
>There are about 30 fields in the output file, most of which come from a
>primary file.  There are a few which come from other files, which I am
>using inner joins to fetch.
>
>
>
>My trouble comes from one of these files, from which I am fetching a 3
>character price code.  This file is keyed by Company (3,0) and Source
>Code (9A).  Unfortunately, it allows more than one price code per
source
>code, although our business rules don't allow this (the file is part of
>a 3rd party software package, so I can't change apply constraints). My
>output file is keyed by Company and Source Code (unique).
>
>
>
>The SQL which doesn't work looks like this:
>
>
>
>Insert into OutFile
>
>Select
>
>Company,
>
>SourceCode,
>
>OtherFields,
>
>PriceCode,
>
>>From SourceFile a inner join PriceFile b
>
>On a.Company = b.Company and a.SourceCode = b.SourceCode.
>
>
>
>Unfortunately, our users have occasionally violated our business rules,
>so the price file has more than one price code associated with some of
>the source codes, which causes a violation of the output file's key
>constraint.
>
>
>
>The only solution which comes to mind is to simply populate a random
>character into the PriceCode field and remove the join altogether.
Then
>I'll run another SQL statement to update my output file, and use the
MIN
>function on the select, as follows:
>
>
>
>First Part:
>
>
>
>Insert into OutFile
>
>Select
>
>            Company
>
>            SourceCode,
>
>            OtherFields,
>
>            'Z'
>
>>From SourceFile.
>
>
>
>Second Part:
>
>
>
>Update OutFile
>
>Set Outfile.PriceCode = select min(PriceCode) from PriceFile
>
>Where PriceFile.SourceCode = OutFile.SourceCode.
>
>
>
>I was wondering if anyone has any bright ideas on how I could
accomplish
>this with just the one SQL statement.
>
>
>
>Thanks
>
>
>
>Greg Fleming
>
>Programmer/Analyst
>
>Everglades Direct, Inc.
>
>
>
>--
>This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
>To post a message email: MIDRANGE-L@xxxxxxxxxxxx
>To subscribe, unsubscribe, or change list options,
>visit: http://lists.midrange.com/mailman/listinfo/midrange-l
>or email: MIDRANGE-L-request@xxxxxxxxxxxx
>Before posting, please take a moment to review the archives
>at http://archive.midrange.com/midrange-l.
>
>
>
>
>--
>This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
>To post a message email: MIDRANGE-L@xxxxxxxxxxxx
>To subscribe, unsubscribe, or change list options,
>visit: http://lists.midrange.com/mailman/listinfo/midrange-l
>or email: MIDRANGE-L-request@xxxxxxxxxxxx
>Before posting, please take a moment to review the archives
>at http://archive.midrange.com/midrange-l.
>--
>This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
>To post a message email: MIDRANGE-L@xxxxxxxxxxxx
>To subscribe, unsubscribe, or change list options,
>visit: http://lists.midrange.com/mailman/listinfo/midrange-l
>or email: MIDRANGE-L-request@xxxxxxxxxxxx
>Before posting, please take a moment to review the archives
>at http://archive.midrange.com/midrange-l.