Thread: Access query problem
02-08-04, 10:37 #1Registered User
- Join Date
- Jan 2004
Unanswered: Access query problem
I have this access query to return receipts, in a linked oracle database, which have not been distributed to a case.
The receipts may be identified in two ways, either by member id or by case number. I have something wrong in the query, because when it is run, it only returns receipts which are case identified and does not pick up the member id identified receipts.
Would you please give it a look to try and help me determine what might be wrong here. Thanks.
SUM(A.AMT_TO_DISTRIBUTE) AS [HELD AMOUNT]
FROM (NOLDBA_INT_CASE_STATUS AS B INNER JOIN NOLDBA_RECEIPT AS A ON B.CASE_ID=A.ID_CASE)
INNER JOIN NOLDBA_INT_CASE_MEMBER AS Z ON A.ID_PAYOR=Z.MEMBER_ID
AND Not Exists
(Select 1 FROM NOLDBA_RECEIPT K
where A.dt_batch = K.dt_batch
ANd A.no_batch = K.no_batch
And A.cd_source_batch = K.cd_source_batch
And A.seq_receipt = K.seq_receipt
And K.ind_backout = 'Y'
And K.dt_end_validity = #12/31/9999#
And ((trim(A.Id_Case) IS NOT NULL
AND A.Id_case = B.case_id )))
( trim(A.Id_Case) IS NULL
AND B.case_id = (Select Min(Y.case_id)
Where A.Id_Payor = Z.member_id
And Z.relation_code IN ('A','P')
And Z.case_id = Y.case_id ))
02-09-04, 08:09 #2Registered User
- Join Date
- Sep 2003
I can't determine enough from your select statement to define 'case identified' or 'member id identified'. I also can't tell how you're addressing the Oracle data, is this a query on an ODBC linked table or the SQL for a recordset tied to an Oracle ADO connection? I usu prefer the latter.
Not sure what version of oracle you're using either, I only have experience with 8 and 9i where dates aren't delimited like they are in Access (#), they're delimited with single quotes and look more like '31-DEC-99' than #12/31/9999#. Have you tried the single quote approach.All code ADO/ADOX unless otherwise specified.