Oracle ORA-01785 오류 원인과 해결 방법 완벽 가이드

ORA-01785 ORDER BY item must be the number of a SELECT-list expression 는?

ORA-01785 에러는 ORDER BY 절에서 사용한 숫자 인덱스가 SELECT 목록의 컬럼 수를 초과하거나, UNION, INTERSECT, MINUS 등의 집합 연산자(Set Operator)를 사용하는 쿼리에서 ORDER BY 절에 컬럼 이름 대신 올바르지 않은 표현식을 사용할 때 발생합니다. Oracle은 집합 연산자가 포함된 복합 쿼리에서 ORDER BY에 컬럼 별칭(Alias) 또는 숫자 위치 인덱스만 허용하며, 개별 테이블의 컬럼 이름을 직접 참조할 수 없습니다. 특히 UNION 쿼리 작성 시 가장 빈번하게 발생하는 에러로, 쿼리 작성 규칙을 정확히 이해하지 못한 상태에서 발생하는 경우가 많습니다.

---

주요 발생 원인

1. UNION/UNION ALL/INTERSECT/MINUS 쿼리에서 테이블 컬럼 이름으로 ORDER BY 사용

집합 연산자를 포함한 복합 쿼리에서는 ORDER BY 절에 특정 테이블의 컬럼명을 직접 사용할 수 없습니다. Oracle은 집합 연산자 쿼리의 결과 집합 전체에 대해 정렬을 수행하기 때문에, 특정 서브쿼리에 속한 테이블 컬럼 이름이 아닌, 최종 결과의 SELECT 목록에 정의된 컬럼 별칭 또는 숫자 인덱스만 ORDER BY에 사용할 수 있습니다.

-- 에러 발생 예시
SELECT emp_id, emp_name FROM employees
UNION
SELECT dept_id, dept_name FROM departments
ORDER BY employees.emp_name;  -- ORA-01785 발생!

2. ORDER BY 절의 숫자 인덱스가 SELECT 목록의 컬럼 수를 초과

ORDER BY에 숫자 위치 인덱스를 사용할 때, 해당 숫자가 SELECT 목록의 실제 컬럼 개수보다 크면 ORA-01785 에러가 발생합니다. 예를 들어 SELECT 절에 컬럼이 2개뿐인데 ORDER BY 5를 사용하면 Oracle은 참조할 컬럼을 찾을 수 없어 에러를 반환합니다.

-- 에러 발생 예시
SELECT emp_id, emp_name FROM employees
UNION
SELECT dept_id, dept_name FROM departments
ORDER BY 5;  -- SELECT 목록에는 2개 컬럼만 있으므로 ORA-01785 발생!

3. 집합 연산자 쿼리에서 ORDER BY에 계산식 또는 함수 직접 사용

UNION 등의 집합 연산자를 사용하는 쿼리에서 ORDER BY 절에 TO_DATE(), UPPER() 등의 함수나 산술 연산식을 직접 사용하면 에러가 발생합니다. Oracle은 집합 연산자 쿼리의 ORDER BY에서 외부 함수 표현식을 허용하지 않으며, 반드시 SELECT 목록의 컬럼 별칭이나 숫자 위치만 허용합니다.

-- 에러 발생 예시
SELECT emp_id, hire_date FROM employees
UNION
SELECT dept_id, created_date FROM departments
ORDER BY TO_CHAR(hire_date, 'YYYY-MM-DD');  -- ORA-01785 발생!

---

해결 방법

원인 1 해결: 컬럼 별칭(Alias) 또는 숫자 인덱스 사용

집합 연산자 쿼리에서는 첫 번째 SELECT 절에 컬럼 별칭을 명시하고, ORDER BY에서 해당 별칭이나 숫자 인덱스를 사용합니다.

-- 올바른 방법 1: 컬럼 별칭 사용
SELECT emp_id AS id, emp_name AS name FROM employees
UNION
SELECT dept_id, dept_name FROM departments
ORDER BY name;  -- 첫 번째 SELECT의 별칭 사용 가능

-- 올바른 방법 2: 숫자 인덱스 사용
SELECT emp_id, emp_name FROM employees
UNION
SELECT dept_id, dept_name FROM departments
ORDER BY 2;  -- 두 번째 컬럼 기준 정렬

원인 2 해결: 올바른 숫자 인덱스 범위 사용

ORDER BY에 숫자 인덱스를 사용할 때는 반드시 SELECT 목록의 컬럼 수 이내의 값을 사용해야 합니다.

-- 올바른 방법: SELECT 목록 컬럼 수(2개) 이내의 인덱스 사용
SELECT emp_id, emp_name FROM employees
UNION
SELECT dept_id, dept_name FROM departments
ORDER BY 1;  -- 첫 번째 컬럼 기준 정렬 (정상)

ORDER BY 2;  -- 두 번째 컬럼 기준 정렬 (정상)

원인 3 해결: 인라인 뷰(Subquery)를 활용한 정렬

함수나 표현식을 기반으로 정렬이 필요한 경우, 집합 연산 전체를 인라인 뷰로 감싸고 외부에서 ORDER BY를 적용하는 방법을 사용합니다.

-- 올바른 방법: 인라인 뷰 사용
SELECT id, target_date
FROM (
    SELECT emp_id AS id, hire_date AS target_date FROM employees
    UNION
    SELECT dept_id, created_date FROM departments
)
ORDER BY TO_CHAR(target_date, 'YYYY-MM-DD');  -- 외부에서 함수 적용 가능

-- 또는 별칭을 활용한 방법
SELECT id, TO_CHAR(target_date, 'YYYY-MM-DD') AS formatted_date
FROM (
    SELECT emp_id AS id, hire_date AS target_date FROM employees
    UNION
    SELECT dept_id, created_date FROM departments
)
ORDER BY formatted_date;

실무 응용 예제: 복잡한 UNION 쿼리 정렬

-- 실무에서 자주 쓰이는 패턴
SELECT *
FROM (
    SELECT
        e.emp_id        AS entity_id,
        e.emp_name      AS entity_name,
        e.salary        AS amount,
        'EMPLOYEE'      AS entity_type,
        e.hire_date     AS ref_date
    FROM employees e
    WHERE e.status = 'ACTIVE'

    UNION ALL

    SELECT
        d.dept_id,
        d.dept_name,
        d.budget,
        'DEPARTMENT',
        d.created_date
    FROM departments d
    WHERE d.is_active = 'Y'
)
ORDER BY entity_type, amount DESC, ref_date;

---

예방 방법

1. UNION 쿼리 작성 시 첫 번째 SELECT에 명확한 컬럼 별칭 부여

집합 연산자를 사용하는 모든 쿼리에서는 첫 번째 SELECT 절에 반드시 의미 있는 컬럼 별칭을 지정하는 습관을 들이세요. Oracle은 집합 연산자 쿼리에서 첫 번째 SELECT의 컬럼 별칭을 최종 결과 컬럼 이름으로 사용하므로, 별칭이 명확할수록 ORDER BY 작성도 직관적이고 안전해집니다. 코드 리뷰 기준에 “UNION 쿼리의 첫 번째 SELECT는 반드시 별칭 사용”을 포함시키는 것을 권장합니다.

2. 집합 연산자 쿼리는 인라인 뷰로 감싸는 패턴을 표준화

복잡한 정렬 조건이나 추가 필터링이 필요한 경우, 집합 연산자 쿼리 전체를 인라인 뷰(서브쿼리)로 감싸고 외부 쿼리에서 ORDER BY와 WHERE를 처리하는 패턴을 팀 표준으로 채택하세요. 이 패턴은 ORA-01785뿐 아니라 집합 연산자 관련 다양한 에러를 예방하고, 쿼리의 가독성과 유지보수성도 크게 향상시킵니다.

---

관련 에러

• ORA-01786: FOR UPDATE 절이 집합 연산자와 함께 사용될 때 발생하는 에러로, 집합 연산자 쿼리의 제약 사항과 관련됩니다.
• ORA-01789: 집합 연산자를 사용하는 쿼리에서 각 SELECT 절의 컬럼 수가 일치하지 않을 때 발생하는 result queries have different number of columns 에러입니다.
• ORA-01790: 집합 연산자 쿼리에서 대응하는 컬럼 간 데이터 타입이 호환되지 않을 때 발생하는 expression must have same datatype as corresponding expression 에러로, UNION 쿼리 작성 시 함께 주의해야 합니다.

---

본 포스트는 AI가 생성한 기술 가이드입니다. 운영 환경 적용 전 충분한 검토를 권장합니다.