> 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 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
>
I suppose this is because the columns in the except are the same that
the ones in the main select and the order by get confused.
i'm redirecting to hackers to know if this is a known bug or there is
something wrong in the select? i don't see anything wrong!!
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)