Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Posts
    37

    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.

    SELECT
    B.CASE_ID,
    B.IV_D_DO_CODE,
    B.WORKER_ID,
    A.ID_PAYOR,
    A.DT_BATCH,
    A.CD_SOURCE_BATCH,
    A.NO_BATCH,
    A.SEQ_RECEIPT,
    A.CD_REASON_STATUS,
    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
    WHERE
    (((A.DT_END_VALIDITY)=#12/31/9999#)
    And ((A.CD_STATUS_RECEIPT)="H")
    AND(( A.Dt_Distribute)<#01/01/1980#))
    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 )))
    Or
    ( trim(A.Id_Case) IS NULL
    AND B.case_id = (Select Min(Y.case_id)
    from
    NOLDBA_INT_CASE_STATUS Y,
    NOLDBA_INT_CASE_MEMBER Z
    Where A.Id_Payor = Z.member_id
    And Z.relation_code IN ('A','P')
    And Z.case_id = Y.case_id ))

    GROUP BY
    B.CASE_ID,
    B.IV_D_DO_CODE,
    B.WORKER_ID,
    A.ID_PAYOR,
    A.DT_BATCH,
    A.CD_SOURCE_BATCH,
    A.NO_BATCH,
    A.SEQ_RECEIPT,
    A.CD_REASON_STATUS

    ORDER BY
    B.IV_D_DO_CODE,
    B.WORKER_ID

  2. #2
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    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.
    Mike.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •