× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



How about:

Insert into OutFile
Select 
  Company,
  SourceCode,
  OtherFields,
  PriceCode,
>From SourceFile a inner join 
     (select company, sourcecode, min(pricecode) 
      from PriceFile 
      group by company, sourcecode) b 
     On a.Company = b.Company and a.SourceCode = b.SourceCode

Elvis

-----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.

 


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.