Geek asks and answers

When are aliases needed in a multi-table conditional insert?

An example from my Oracle Certified Expert Exam Tutorial examines me with bewilderment.

SQL statement:

INSERT
  WHEN (BOSS_SALARY-EMPLOYEE_SALARY < 10000) THEN
    INTO SALARY_CHART (EMP_TITLE, SUPERIOR, EMP_INCOME, SUP_INCOME)
    VALUES (EMPLOYEE, BOSS, EMPLOYEE_SALARY, BOSS_SALARY)
SELECT A.POSITION EMPLOYEE
  , B.POSITION BOSS
  , A.MAX_SALARY EMPLOYEE_SALARY
  , B.MAX_SALARY BOSS_SALARY
FROM POSITIONS A
  JOIN POSITIONS B ON A.REPORTS_TO_POSITION_ID = B.POSITION_ID

Explanation from the book:

Note that this version has done more than required, and applies the alias column to each column in the subquery, then refers to this column aliases from WHEN and VALUES of the article. We only need the alias column A.POSITION and B.POSITION in rows 5 and 6, so we can refer to the column aliases in row 4.

My source of confusion:

Can't we need the aliases in the two MAX_SALARY columns selected in rows 7 and 8 to distinguish them in row 4? The explanation seems to suggest that the aliases EMPLOYEE_SALARY and BOSS_SALARY are extraneous ...

- ?

+3
1

, , (, A.POSITION A.MAX_SALARY), . , :

INSERT
  WHEN (MAX_SALARY-EMPLOYEE_SALARY < 10000) THEN
    INTO SALARY_CHART (EMP_TITLE, SUPERIOR, EMP_INCOME, SUP_INCOME)
    VALUES (EMPLOYEE, POSITION, EMPLOYEE_SALARY, MAX_SALARY)
SELECT A.POSITION EMPLOYEE
  , B.POSITION 
  , A.MAX_SALARY EMPLOYEE_SALARY
  , B.MAX_SALARY 
FROM POSITIONS A
  JOIN POSITIONS B ON A.REPORTS_TO_POSITION_ID = B.POSITION_ID
+2

More articles:


All Articles