Jobtiens lerreur dans la requête suivante:
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); Réponse
Vous avez besoin dun alias pour les deux tables dérivées. Et la paire externe autour de est 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 Mais la jointure nest pas nécessaire en premier lieu. Votre requête peut être simplifiée comme suit:
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; Vous navez pas besoin de sélectionner first_name, last_name et postal_code dans la sélection interne car vous ne les utilisez pas dans la sélection externe. Cela peut rendre la requête potentiellement plus efficace.
De plus, la condition unq.last_name = "" ne fera pas ce que vous pensez quelle fait. Oracle na pas de « chaîne vide » « . Une chaîne de longueur zéro ("") sera stockée sous NULL, donc ce que vous voulez vraiment est probablement:
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;