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

    Unanswered: query with or in the where clause

    Could someone give this a look and show me how to find cases where one of these or both are present. (full query below)

    WHERE
    (((NOLDBA_OBLIGATION.AMT_PERIODIC)>0)
    AND ((NOLDBA_OBLIGATION.DT_END_OBLIGATION)>[GREATER THAN OBLIGATION END DATE])
    AND ((NOLDBA_OBLIGATION.DT_ACCRUAL_NEXT)>[GREATER THAN NEXT ACCRUAL DATE]))

    OR
    [CASE BALANCE].BALANCE > 0

    I have no problem with finding cases where the obligation is greater than 0 and end obligation is greater than date entered and next accrual date greater than the date entered.

    But where this critera is not present, I want to find cases where the balance is greater than 0. I just don't know how to put them together in one query.


    This is the query right now:

    SELECT DISTINCT
    ([NOLDBA_INT_CASE_STATUS].[CASE_ID]) AS [CASE ID],
    NOLDBA_INT_CASE_STATUS.IV_D_DO_CODE,
    NOLDBA_INT_CASE_STATUS.WORKER_ID,
    Sum(NOLDBA_OBLIGATION.AMT_PERIODIC) AS [PERIODIC AMT],
    [CASE BALANCE].BALANCE

    FROM
    (NOLDBA_INT_CASE_STATUS INNER JOIN NOLDBA_OBLIGATION ON NOLDBA_INT_CASE_STATUS.CASE_ID = NOLDBA_OBLIGATION.ID_CASE)
    INNER JOIN [CASE BALANCE] ON NOLDBA_INT_CASE_STATUS.CASE_ID = [CASE BALANCE].ID_CASE

    WHERE
    (((NOLDBA_OBLIGATION.AMT_PERIODIC)>0)
    AND ((NOLDBA_OBLIGATION.DT_END_OBLIGATION)>[GREATER THAN OBLIGATION END DATE])
    AND ((NOLDBA_OBLIGATION.DT_ACCRUAL_NEXT)>[GREATER THAN NEXT ACCRUAL DATE]))

    GROUP BY
    ([NOLDBA_INT_CASE_STATUS].[CASE_ID]),
    NOLDBA_INT_CASE_STATUS.IV_D_DO_CODE,
    NOLDBA_INT_CASE_STATUS.WORKER_ID,
    [CASE BALANCE].BALANCE

    ORDER BY
    NOLDBA_INT_CASE_STATUS.IV_D_DO_CODE,
    NOLDBA_INT_CASE_STATUS.WORKER_ID;

  2. #2
    Join Date
    Feb 2004
    Location
    Binary Universe
    Posts
    57
    Try this:

    add a left parenthesis after 'WHERE' and a right one after the whole 'ANDs' block followed by ‘OR…’

    WHERE
    ( (((NOLDBA_OBLIGATION.AMT_PERIODIC)>0)
    AND ((NOLDBA_OBLIGATION.DT_END_OBLIGATION)>[GREATER THAN OBLIGATION END DATE])
    AND ((NOLDBA_OBLIGATION.DT_ACCRUAL_NEXT)>[GREATER THAN NEXT ACCRUAL DATE])) ) OR [CASE BALANCE].BALANCE > 0
    Last edited by The Byte; 02-17-04 at 03:20.
    I won't byte... hard!

Posting Permissions

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