Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2003
    Posts
    13

    Question Unanswered: Queries with different results...

    Hi,
    I am getting a difference of '1' when I am running the queries which I am listing below...
    1)
    select count(distinct(I.order_ID)) as completedTotal
    from T_INVOICES I, T_ORDERS O,T_STATUS S
    where
    O.order_Id = I.order_ID

    and I.feeDate >= {d '2002-08-01'} and I.feeDate < {d '2002-09-01'}
    AND client_ID NOT IN(14,693) AND NOT EXISTS(SELECT Order_ID FROM T_STATUS WHERE
    order_id = O.order_ID AND StatusType_ID = 7)
    __________________________________________________ __________
    2)
    select count(distinct(I.order_ID)) as completedTotal
    from T_INVOICES I
    LEFT OUTER JOIN T_ORDERS AS O ON (O.order_Id = I.order_ID)
    LEFT OUTER JOIN T_STATUS AS S ON (s.Order_ID = O.order_id)

    WHERE I.feeDate >= {d '2002-08-01'} and I.feeDate < {d '2002-09-01'}
    AND client_ID NOT IN(14,693) AND NOT EXISTS(SELECT Order_ID FROM T_STATUS WHERE order_id = O.order_ID AND StatusType_ID = 7)

    can anyone help inresolving this.

    thanks
    Last edited by smahamkl; 07-14-03 at 14:44.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    In your first query, table S is not joined to table O, resulting in a cross-join.

    blindman

  3. #3
    Join Date
    Jul 2003
    Posts
    13
    could you say how to optimize the second query?

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, I would script it like this for clarity, but this is just my style (it looks better once you paste it without the line-wrapping):

    select count(distinct(I.order_ID)) as completedTotal
    from T_INVOICES
    LEFT OUTER JOIN T_ORDERS on T_ORDERS.order_Id = T_INVOICES.order_ID
    LEFT OUTER JOIN T_STATUS on T_STATUS.Order_ID = T_ORDERS.order_id
    WHERE T_INVOICES.feeDate '2002-08-01' and T_INVOICES.feeDate < '2002-09-01'
    AND client_ID NOT IN(14,693)
    AND NOT EXISTS (SELECT Order_ID FROM T_STATUS WHERE order_id = T_ORDERS.order_ID AND StatusType_ID = 7)

    I've never liked using short aliases for tables because I've never thought the time saved in typing was worth the cost in readability.

    You can optimize your query by indexing the join columns and criteria columns: order_id, feeDate, client_ID, and StatusType, but it may not be necessary to index all of them to get good performance.

    Make sure you understand how your NOT EXISTS clause is going to affect your output. It will exclude all invoice and order records where there is a status recrod = 7, even if there are additional associated status records that do not = 7. If you just want to exclude status records = 7 from your output, put your criteria in the join:

    select count(distinct(I.order_ID)) as completedTotal
    from T_INVOICES
    LEFT OUTER JOIN T_ORDERS on T_ORDERS.order_Id = T_INVOICES.order_ID
    LEFT OUTER JOIN T_STATUS on T_STATUS.Order_ID = T_ORDERS.order_id T_STATUS.StatusType_ID <> 7
    WHERE T_INVOICES.feeDate '2002-08-01' and T_INVOICES.feeDate < '2002-09-01'
    AND client_ID NOT IN(14,693)

    Also, I don't know how you have your relationships set up, but by the way you have three tables joined on order_id I suspect you are violating database normalization guidelines. (Unless the orders table has a one-to-many relationship with both Invoices and Status tables.)

    blindman

  5. #5
    Join Date
    Jul 2003
    Posts
    13
    Hi,
    I am using the query,

    select count(distinct(T_INVOICES.order_ID)) as completedTotal
    from T_INVOICES
    LEFT OUTER JOIN T_ORDERS on T_ORDERS.order_Id = T_INVOICES.order_ID
    LEFT OUTER JOIN T_STATUS on T_STATUS.Order_ID = T_ORDERS.order_id and T_STATUS.StatusType_ID <> 7
    where
    T_INVOICES.feeDate >= {d '2002-08-01'} and T_INVOICES.feeDate < {d '2002-09-01'}
    AND client_ID NOT IN(14,693)
    __________________________________________________ ___

    but i am getting the number so high of the actual. Actually I have to get '29' instead I am getting '46'. Can you explain it.

    thanks,
    siva

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Instead of:
    select count(distinct(T_INVOICES.order_ID)) as completedTotal

    try this:
    select distinct T_INVOICES.order_ID

    ...in order to see what data is actually being counted. I'm not sure your count(distinct( syntax is going to work the way you are expecting it to.

    Also, try this:
    select count(distinct T_INVOICES.order_ID) as completedTotal

    The extra parentheses you have aren't necessary and may be affected your results.

    blindman

  7. #7
    Join Date
    Jul 2003
    Posts
    13
    still iam getting the same result...

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    So how many rows were returned by "select distinct T_INVOICES.order_ID"? Were any of them duplicates? Were any there that weren't supposed to be there?

    Your problem my be due to, or compounded by, the relationships established between your tables. What is the architecture of these three tables:

    Invoices -> Orders -> Status?

    or

    Invoices <- Orders -> Status?

    You may need to try building your query from scratch again, starting with a simple select from Invoices and then adding joins and criteria as you verify that you are getting the data you expect. Your query design has some subtle joins and criteria, but I can't tell if these are required to get the results you want, or if they are just coding issues to be cleaned up.

    blindman

Posting Permissions

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