Otrzymuję błąd w następującym zapytaniu:
SELECT DISTINCT UNIQUE_ID as uid, CONFIDENCE_IS_SAME FROM ( (SELECT UNIQUE_ID, CONFIDENCE_IS_SAME, FIRST_NAME, LAST_NAME, POSTAL_CODE FROM DANIEL.UNIQUE_PHYSICIAN WHERE DANIEL.UNIQUE_PHYSICIAN.FIRST_NAME = "" AND DANIEL.UNIQUE_PHYSICIAN.LAST_NAME = "" AND DANIEL.UNIQUE_PHYSICIAN.IS_ROOT_PHYS = 0 AND DANIEL.UNIQUE_PHYSICIAN.POSTAL_CODE = "") INNER JOIN (SELECT MAX(CONFIDENCE_IS_SAME) OVER (PARTITION BY ROOT_ID) max_conf FROM DANIEL.UNIQUE_PHYSICIAN WHERE DANIEL.UNIQUE_PHYSICIAN.FIRST_NAME = "" AND DANIEL.UNIQUE_PHYSICIAN.LAST_NAME = "" AND DANIEL.UNIQUE_PHYSICIAN.IS_ROOT_PHYS = 0 AND DANIEL.UNIQUE_PHYSICIAN.POSTAL_CODE = "") ON CONFIDENCE_IS_SAME = max_conf); Odpowiedź
Potrzebujesz aliasu dla obu tabel pochodnych. A zewnętrzna para wokół jest bezużyteczna.
SELECT DISTINCT t1.unique_id AS uid, t1.confidence_is_same FROM ( --<< only one opening parenthesis SELECT unique_id, confidence_is_same, first_name, last_name, postal_code FROM daniel.unique_physician WHERE daniel.unique_physician.first_name = "" AND daniel.unique_physician.last_name = "" AND daniel.unique_physician.is_root_phys = 0 AND daniel.unique_physician.postal_code = "" ) t1 ---<< the alias for the derived table is missing INNER JOIN ( SELECT max(confidence_is_same) OVER (PARTITION BY root_id) max_conf FROM daniel.unique_physician WHERE daniel.unique_physician.first_name = "" AND daniel.unique_physician.last_name = "" AND daniel.unique_physician.is_root_phys = 0 AND daniel.unique_physician.postal_code = "" ) t2 ON t1.confidence_is_same = t2.max_conf Ale łączenie nie jest potrzebne w pierwszej kolejności. Twoje zapytanie można uprościć do:
SELECT DISTINCT t1.unique_id AS uid, t1.confidence_is_same FROM ( SELECT unique_id, confidence_is_same, max(confidence_is_same) OVER (PARTITION BY root_id) max_conf FROM daniel.unique_physician unq WHERE unq.first_name = "" AND unq.last_name = "" AND unq.is_root_phys = 0 AND unq.postal_code = "" ) t1 where confidence_is_same = max_conf; Nie musisz wybierać first_name, last_name i postal_code w wewnętrznym wyborze, ponieważ nie używasz ich w zewnętrznym zaznaczaniu. Może to uczynić zapytanie potencjalnie bardziej wydajnym.
Ponadto warunek unq.last_name = "" nie zrobi tego, co myślisz, że robi. Oracle nie ma „pustego ciągu” „. Ciąg o długości zero ("") zostanie zapisany jako NULL, więc to, czego naprawdę potrzebujesz, to prawdopodobnie:
SELECT DISTINCT t1.unique_id AS uid, t1.confidence_is_same FROM ( SELECT unique_id, confidence_is_same, max(confidence_is_same) OVER (PARTITION BY root_id) max_conf FROM daniel.unique_physician unq WHERE unq.first_name is null AND unq.last_name is null AND unq.is_root_phys = 0 AND unq.postal_code is null ) t1 where confidence_is_same = max_conf;