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.

thanksIn your first query, table S is not joined to table O, resulting in a cross-join.

blindman|||could you say how to optimize the second query?|||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|||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|||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|||still iam getting the same result...|||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.

blindmansql