Unfortunately, my biggest problem is that the database is NOT normalized. Here is the SQL that I've come up with, but it only works for some records. Sometimes it doesn't return anything (even though I know that there is one record in CMPARRST) and sometimes it returns double the number of records:
SELECT CMPARRST."BOOKING#", ARHDAT, ARRTIM, ARROFF, ARROFN,
CMPAGNCY.AGNAME, ARRLOC, BNDSET, BNDPAY, RECVDAT, 'Cash' AS BONDTYPE, 'None' AS POSNAM
FROM CMPARRST
INNER JOIN CMPAGNCY ON CMPARRST.ARRAGC = CMPAGNCY.AGENCY
LEFT OUTER JOIN ACPCBDMF ON CMPARRST."BOOKING#" = ACPCBDMF."BOOKING#"
WHERE CMPARRST.CASPRE = :CASPREC AND CMPARRST.CASNUM = :CASNUMC
AND DEFSEQ =

EFSEQC
UNION
SELECT CMPARRST."BOOKING#", ARHDAT, ARRTIM, ARROFF, ARROFN,
CMPAGNCY.AGNAME, ARRLOC, BNDSET, BNDPAY, TRANDATE AS RECVDAT, 'Surety' AS BONDTYPE, POSNAM
FROM CMPARRST
INNER JOIN CMPAGNCY ON CMPARRST.ARRAGC = CMPAGNCY.AGENCY
LEFT OUTER JOIN ACPSBDMF ON CMPARRST."BOOKING#" = ACPSBDMF."BOOKING#"
INNER JOIN ACPSBDTF ON ACPSBDMF.BNDNUM = ACPSBDTF.BNDNUM
WHERE CMPARRST.CASPRE = :CASPRES AND CMPARRST.CASNUM = :CASNUMS
AND DEFSEQ =

EFSEQS
ORDER BY ARHDAT
(trust me I don't need a beginner SQL book!)
Is there a way that I can query a query? I'm trying to avoid creating a "custom" table just for this information.
Thanks,
Leslie