Обсуждение: Issue with adding ORDER BY to EXCEPT.
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
> 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 ;)
On Tue, 2005-06-07 at 12:16, Jaime Casanova wrote: > > 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!! No, it's because to the order by, the column names are the ones given by the part after the period of the first select. If you do a plain select UNION select with no order by, you'll see the title for the columns is taken from the first select list column names. So, the order by needs to be "order by encounter_id, encounter_d_id
On Tue, Jun 07, 2005 at 12:42:47PM -0500, Scott Marlowe wrote: > On Tue, 2005-06-07 at 12:16, Jaime Casanova wrote: > > 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!! > > No, it's because to the order by, the column names are the ones given by > the part after the period of the first select. If you do a plain select > UNION select with no order by, you'll see the title for the columns is > taken from the first select list column names. > > So, the order by needs to be "order by encounter_id, encounter_d_id Or even easier: order by 1, 2; Then you don't even need to know the column names... -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Вложения
Perfect, Thanks so much to all who assisted me. -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Martijn van Oosterhout Sent: Tuesday, June 07, 2005 10:51 AM To: Scott Marlowe Cc: Jaime Casanova; Tim Vadnais; pgsql-general@postgresql.org; Hackers Subject: Re: [GENERAL] Issue with adding ORDER BY to EXCEPT. On Tue, Jun 07, 2005 at 12:42:47PM -0500, Scott Marlowe wrote: > On Tue, 2005-06-07 at 12:16, Jaime Casanova wrote: > > 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!! > > No, it's because to the order by, the column names are the ones given by > the part after the period of the first select. If you do a plain select > UNION select with no order by, you'll see the title for the columns is > taken from the first select list column names. > > So, the order by needs to be "order by encounter_id, encounter_d_id Or even easier: order by 1, 2; Then you don't even need to know the column names... -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.