1

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);
0
3

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;
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.