Hi all,
I've got myself into a vicious loop that I can't seem to get out of.
I have paired down the query for debugging this particular problem that I'm
having.
What I'm trying to do is find all 'encounters' that have no matching record
in 'p_l_d' for a date range.
My first SELECT pulls all 'encounters' and returns the expected rows.
My second SELECT pulls all 'encounters' that have matching records in
'p_l_d' and returns the expected rows. (Which number fewer then the first
select.)
If I add an EXCEPT between the two SELECTs, I get what I suspect is an
unordered list.
If I add the ORDER BY (either on one or two columns) I get an error that I
can't resolve.
The following query represents my query. I have also tried making the FROM
line == "FROM encounter, encounter_d" along with the other accompanying
changes, but that made no difference.
Below the query are my results.
SELECT encounter.encounter_id, encounter_d.encounter_d_id
FROM encounter
JOIN encounter_d on encounter_d.encounter_id = encounter.encounter_id
EXCEPT
SELECT encounter.encounter_id, encounter_d.encounter_d_id
FROM encounter
JOIN encounter_d on encounter_d.encounter_id = encounter.encounter_id
JOIN p_l_d ON p_l_d.patient_id = encounter.patient_mpi
WHERE encounter_d.encounter_id = encounter.encounter_id
AND ((p_l_d.start_date <= encounter_d.from_date OR p_l_d.start_date IS
NULL)
AND (p_l_d.end_date >= encounter_d.from_date OR p_l_d.end_date IS NULL))
ORDER BY encounter.encounter_id, encounter_d.encounter_d_id
With out the ORDER BY
test_client-# ;
encounter_id | encounter_d_id
--------------+----------------
2 | 2
2 | 3
...
463 | 794
463 | 795
466 | 798
466 | 799
(147 rows)
With the ORDER BY
NOTICE: adding missing FROM-clause entry for table "encounter"
NOTICE: adding missing FROM-clause entry for table "encounter_d"
ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of the
result columns
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
