Search Postgresql Archives

Issue with adding ORDER BY to EXCEPT.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux