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.