Anthony Tanas wrote:

> OK, I'm really confused now.
>
> I simply added an Index for PAYMENT.BILLINGID and the query works. WHY?
>
> Without the index it worked in 3 of 4 installations?

Simple, the optimizer uses the data to choose the plan. The data
differed and the optimizer believed (incorrectly) that a different plan
would be better for this one installation. Eventually, it would have
returned the result set (though it could have taken hours).

As for your query, I've never thought about using LEFT JOINs to
implement 'find something in one of several tables' when you don't want
anything from either of the tables in your output. Though it looks
interesting. I tried rewriting your query to an easier understandable
query (for me) this way:

SELECT
DOCTOR.DOCTORID,
DOCTOR.LASTNAME,
DOCTOR.FIRSTNAME,
DOCTOR.MIDDLEINITIAL,
PATIENT.PATIENTID,
PATIENT.CHARTID,
PATIENT.LASTNAME,
PATIENT.FIRSTNAME,
PATIENT.MIDDLEINITIAL
FROM
PATIENT
INNER JOIN BILLING ON PATIENT.PATIENTID = BILLING.PATIENTID
INNER JOIN DOCTOR ON BILLING.DOCTORID = DOCTOR.DOCTORID
WHERE
(SELECT SUM(BILLINGITEM.AMOUNT * BILLINGITEM.UNITS)
FROM BILLINGITEM
WHERE BILLING.BILLINGID = BILLINGITEM.BILLINGID
AND BILLINGITEM.BILLINGITEMDATE = :BILLINGITEMDATE) <> 0 OR
(SELECT SUM(PAYMENT.AMOUNT)
FROM PAYMENT
WHERE BILLING.BILLINGID = PAYMENT.BILLINGID
AND PAYMENT.PAYMENTDATE = :BILLINGITEMDATE) <> 0 OR
(SELECT SUM(REFUND.AMOUNT)
FROM REFUND
WHERE BILLING.BILLINGID = REFUND.BILLINGID
AND REFUND.REFUNDDATE = :BILLINGITEMDATE) <> 0
ORDER BY
DOCTOR.LASTNAME,
PATIENT.LASTNAME,
PATIENT.FIRSTNAME

but found that it wasn't exactly the same (my query e.g. considers
SUM(REFUND.AMOUNT) <> 0 only where the REFUNDDATE is appropriate, your
query considers SUM(REFUND.AMOUNT) <> 0 also if BILLINGITEMDATE or
PAYMENTDATE is appropriate, regardless of REFUNDDATE).

I don't know your data, and don't know if things could easily be
simplified or not. I take it you have both positive and negative values
in the SUMMED fields (if not, simply using EXISTS is far simpler)? In
general, my above suggestion seems rather resource hungry having to run
SUM (subselect) 1 - 3 times for every potential row, and I wouldn't be
surprised if your original query is too.

HTH,
Set

> Here is the query again:
>
> SELECT DISTINCT
> DOCTOR.DOCTORID,
> DOCTOR.LASTNAME,
> DOCTOR.FIRSTNAME,
> DOCTOR.MIDDLEINITIAL,
> PATIENT.PATIENTID,
> PATIENT.CHARTID,
> PATIENT.LASTNAME,
> PATIENT.FIRSTNAME,
> PATIENT.MIDDLEINITIAL
> FROM
> PATIENT
> INNER JOIN BILLING ON (PATIENT.PATIENTID = BILLING.PATIENTID)
> INNER JOIN BILLINGITEM ON (BILLING.BILLINGID = BILLINGITEM.BILLINGID)
> LEFT OUTER JOIN PAYMENT ON (BILLING.BILLINGID = PAYMENT.BILLINGID)
> LEFT OUTER JOIN REFUND ON (BILLING.BILLINGID = REFUND.BILLINGID)
> INNER JOIN DOCTOR ON (BILLING.DOCTORID = DOCTOR.DOCTORID)
> WHERE
> BILLINGITEM.BILLINGITEMDATE = :BILLINGITEMDATE OR
> PAYMENT.PAYMENTDATE = :BILLINGITEMDATE OR
> REFUND.REFUNDDATE = :BILLINGITEMDATE
> GROUP BY
> DOCTOR.DOCTORID,
> DOCTOR.LASTNAME,
> DOCTOR.FIRSTNAME,
> DOCTOR.MIDDLEINITIAL,
> PATIENT.PATIENTID,
> PATIENT.CHARTID,
> PATIENT.LASTNAME,
> PATIENT.FIRSTNAME,
> PATIENT.MIDDLEINITIAL
> HAVING
> SUM(PAYMENT.AMOUNT) <> 0 OR
> SUM(BILLINGITEM.AMOUNT * BILLINGITEM.UNITS) <> 0 OR
> SUM(REFUND.AMOUNT) <> 0
> ORDER BY
> DOCTOR.LASTNAME,
> PATIENT.LASTNAME,
> PATIENT.FIRSTNAME