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

    Unanswered: query syntax error message

    I have this query that I'm trying to convert from SQL to be used in Access, but the first problem that I ran into was a syntax error message (I have attached). It appear there may be several problems here, but this is the first I have run into...I'm sure more will follow. Please give it a look and advise if you have the time. Thanks!

    SELECT
    COUNT (B.CASE_ID),
    SUM (A.AMT_TO_DISTRIBUTE) AS [HELD AMOUNT],
    NOLDBA_INT_CASE_STATUS.IV_D_DO_CODE,
    NOLDBA_RECEIPT.CD_REASON_STATUS

    FROM NOLDBA_INT_CASE_STATUS B INNER JOIN NOLDBA_RECEIPT A ON B.CASE_ID =A.ID_CASE

    WHERE (((A.CD_STATUS_RECEIPT)="H") AND ((A.DT_DISTRIBUTE)=#1/1/2001#) AND ((A.DT_END_VALIDITY)=#12/31/9999#))

    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, NOLDBA.INT_CASE_MEMBER
    WHERE A.ID_PAYOR = Z.MEMBER_ID
    AND Z.RELATION_CODE IN ('A', 'P')
    AND Z.CASE_ID = Y.CASE_ID)))

    GROUP BY IV_D_DO_CODE,
    CD_REASON_STATUS
    ORDER BY IV_D_DO_CODE;
    Attached Thumbnails Attached Thumbnails syntax error.jpg  

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    Pruebe this one:
    Dim qdf As String

    qdf = " SELECT " & _
    "COUNT (B.CASE_ID)," & _
    " SUM (A.AMT_TO_DISTRIBUTE) AS [HELD AMOUNT]," & _
    " NOLDBA_INT_CASE_STATUS.IV_D_DO_CODE," & _
    " NOLDBA_RECEIPT.CD_REASON_STATUS" & _
    " FROM NOLDBA_INT_CASE_STATUS B INNER JOIN NOLDBA_RECEIPT A ON B.CASE_ID =A.ID_CASE" & _
    " WHERE (((A.CD_STATUS_RECEIPT) = 'H') And ((A.DT_DISTRIBUTE) = #1/1/01#) And ((A.DT_END_VALIDITY) = #12/31/9999#))" & _
    " 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 , NOLDBA.INT_CASE_MEMBER" & _
    " WHERE A.ID_PAYOR = Z.MEMBER_ID" & _
    " AND Z.RELATION_CODE IN ('A', 'P')" & _
    " AND Z.CASE_ID = Y.CASE_ID))) GROUP BY IV_D_DO_CODE, CD_REASON_STATUS ORDER BY IV_D_DO_CODE;"

    I hope this help you.
    Saludos
    Norberto

  3. #3
    Join Date
    Jan 2004
    Posts
    37
    It didn't help....still same error.

  4. #4
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    Originally posted by syldania
    It didn't help....still same error.
    Post the code with is the query for see...
    Saludos
    Norberto

  5. #5
    Join Date
    Nov 2003
    Location
    San Francisco, CA USA
    Posts
    59

    Y and Z?

    I've been trying to setup my computer to run your query but I don't see where you've created the Y and Z aliases that you use near the end. What tables are Y and Z?

    Do they belong here?

    " NOLDBA_INT_CASE_STATUS Y, NOLDBA.INT_CASE_MEMBER Z" & _
    " WHERE A.ID_PAYOR = Z.MEMBER_ID" & _

    If so, I'll finish my setup and see what I can figure out about your SQL code.

    Magee
    Last edited by mageem; 02-04-04 at 00:22.

Posting Permissions

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