Ricevo lerrore nella seguente query:
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); Risposta
È necessario un alias per entrambe le tabelle derivate. E la coppia esterna attorno a from è inutile.
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 Ma il join non è necessario in primo luogo. La tua query può essere semplificata in:
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; Non è necessario selezionare first_name, last_name e postal_code nella selezione interna poiché non li utilizzi nella selezione esterna. Ciò può rendere la query potenzialmente più efficiente.
Inoltre, la condizione unq.last_name = "" non farà ciò che pensi. Oracle non ha una “stringa vuota “. Una stringa di lunghezza zero ("") verrà memorizzata come NULL, quindi quello che vuoi veramente è probabilmente:
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;