When are aliases needed in a multi-table conditional insert?
An example from my Oracle SQL Certified Expert Exam study guide has me puzzled.
The 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
The explanation from the book:
Note that this version has done more than is required, and applies column 开发者_高级运维 aliases to each column in the subquery, then references those column aliases from the WHEN and VALUES clauses. We only needed column aliases on A.POSITION and B.POSITION in lines 5 and 6, so we can reference the column aliases in line 4.
My source of confusion:
Don't we also need the aliases on the two MAX_SALARY columns selected in lines 7 and 8, in order to distinguish them in line 4? The explanation seems to suggest the EMPLOYEE_SALARY and BOSS_SALARY aliases are extraneous...
Can someone please clarify this?
I'm guessing they meant they only need it on two columns (eg A.POSITION and A.MAX_SALARY) to disambiguate for the parser. For example, this would work:
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
精彩评论