Re: Duplicate Records from 2 tables

[Gracen Duffield <gduffiel@TDHCA.STATE.TX.US>]

If you want to pull off the duplicate entries from the voucher header table,
here is what I would suggest:

Create a query that selects records from the voucher header table that have
more than one entry for the invoice ID.

begin-select
select
a.voucher_id,
a.invoice_id,
a.invoice_dt,
a.gross_amt
from ps_voucher a
where 1 < (select count(*)
             from ps_voucher b
                where b.invoice_id = a.invoice_id)
        and a.invoice_id != ' '
        order by a.invoice_id

This will return all of the voucher_ids that have more than one voucher for
an invoice_id

To return the ps_voucher_line info and the ps_distrib info, just join the
tables like this example:


SELECT
a.OPRID,
a.VOUCHER_ID,
a.INVOICE_ID,
a.INVOICE_DT,
a.GROSS_AMT,
c.VOUCHER_LINE_NUM,
D.DISTRIB_LINE_NUM,
D.PROJECT_ID,
D.BUDGET_YEAR,
D.PROGRAM_CODE,
D.MERCHANDISE_AMT,
a.VENDOR_ID,
D.DESCR,
c.descr
FROM PS_VOUCHER a,
     PS_DISTRIB_LINE D,
     PS_voucher_Line C
WHERE 1 < (  select count(*)
                  from ps_voucher b
                    where b.invoice_id = a.invoice_id)
and   a.invoice_id != ' '
and   a.BUSINESS_UNIT = 'PROD'
AND   a.BUSINESS_UNIT = D.BUSINESS_UNIT
AND   a.VOUCHER_ID    = D.VOUCHER_ID
AND     a.VOUCHER_ID    = c.voucher_id
and   d.VOUCHER_LINE_NUM = c.VOUCHER_LINE_NUM
order by voucher_id



Gracen Duffield
Texas Department of Housing and Community Affairs
475-3839


-----Original Message-----
From: Lisa Dacumos [mailto:ldacumos@REVEREGROUP.COM]
Sent: Wednesday, May 12, 1999 12:34 PM
To: Multiple recipients of list SQR-USERS
Subject: Duplicate Records from 2 tables


I have a question relating to duplicate records.
Here's the scenario:
I have 2 tables, a voucher table (table1) and its corresponding voucher
history
table (table2).  I would like to pull off any duplicate records from the two
tables as follows (based on 3 keys, invoice_id, invoice_dt, gross_amt):
1. all duplicates that exist w/in table1 only
2. all duplicates that exist w/in tabe2 only
3. all duplicates that exist in both table1 and table2

The following code pulls duplicate records of the voucher table only. When I
tried to add logic for table2, the code bombed.  How should I go about
selecting
for all these records?  Would a union work?  If so, how, b/c I tried adding
UNION but that failed, too.

fyi, i'm using SQLBase Server 6.1.2

Thanks for you help!!
Lisa


begin-SELECT
INVOICE_ID,
INVOICE_DT,
GROSS_AMT
    move &INVOICE_ID to $INVOICE_ID
    move &INVOICE_DT to $INVOICE_DT
    move &GROSS_AMT  to #GROSS_AMT
    move 'N' to $FOUND
    do Get-Voucher-Information
    if $FOUND = 'Y'
        print ' '    (+1,1)
    end-if
FROM PS_VOUCHER
GROUP BY INVOICE_ID, INVOICE_DT, GROSS_AMT
HAVING COUNT(*) > 1
end-select