FROM keyword not found where expected (Oracle) ORA-00923

oracle

I'm getting the error in the following 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);

Best Answer

You need an alias for both derived tables. And the outer pair around the from clause is useless.

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

But the join isn't needed in the first place. Your query can be simplified to:

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;

You don't need to select first_name, last_name and postal_code in the inner select as you don't use them in the outer select. This can make the query potentially more efficient.

Additionally, the condition unq.last_name = '' won't do what you think it does. Oracle does not have an "empty string". A string with length zero ('') will be stored as NULL, so what you really want is probably:

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;
Related Question