Jeg får feilen i følgende spørsmål:
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); Svar
Du trenger et alias for begge avledede tabeller. Og det ytre paret rundt from klausul er ubrukelig.
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 Men sammenføyningen er ikke nødvendig i utgangspunktet. Søket ditt kan forenkles til:
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; Du trenger ikke å velge first_name, last_name og postal_code i det indre valget ettersom du ikke bruker dem i det ytre utvalget. Dette kan gjøre spørringen potensielt mer effektiv.
I tillegg vil tilstanden unq.last_name = "" ikke gjøre det du tror den gjør. Oracle har ikke en «tom streng» «. En streng med lengde null ("") lagres som NULL, så det du virkelig vil ha er sannsynligvis:
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;