开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜