Shulou(Shulou.com)06/01 Report--

When join multiple tables, in the select statement, if you use the using statement, the column selected in the using statement cannot specify a qualifier in the select statement, otherwise the ORA-25154 will be reported

View the emp table

SQL > select * from emp EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO--7369 SMITH CLERK 7902 1980-12-17 800.00 20 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 7566 JONES MANAGER 7839 1981-4-2 2975.00 20 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20 7839 KING PRESIDENT 1981-11-17 5000.00 10 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 7876 ADAMS CLERK 7788 1987/5/23 1100.00 20 7900 JAMES CLERK 7698 1981-12-3 950.00 30 7902 FORD ANALYST 7566 1981-12-3 3000.00 20 7934 MILLER CLERK 7782 1982-1-23 1300.00 10

View the dept table

SQL > select * from dept;DEPTNO DNAME LOC- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON

Add a qualifier in a select statement using the column specified in the using statement

SQL > select e.deptnoree.salred.dname from emp e join dept d using (deptno) ORA-25154: the column part of the USING clause cannot be finite.

When not added

SQL > select deptno,e.sal,d.dname from emp e join dept d using (deptno) DEPTNO SAL DNAME- 10 2450.00 ACCOUNTING 10 5000.00 ACCOUNTING 10 1300.00 ACCOUNTING 20 2975.00 RESEARCH 20 3000.00 RESEARCH 20 1100.00 RESEARCH 20 800.00 RESEARCH 20 3000.00 RESEARCH 30 1250.00 SALES 30 1500.00 SALES 30 1600.00 SALES 30 950. 00 SALES 30 2850.00 SALES 30 1250.00 SALES14 rows selected

When using on, you must specify a qualifier to display correctly, otherwise an error will be reported, indicating that deptno cannot identify which table it is, because both the dept and emp tables have deptno columns

Select deptno,e.sal,d.dname from emp e join dept d on (e.deptno=d.deptno); select deptno,e.sal,d.dname from emp e join dept d on (e.deptno=d.deptno) ORA-00918: undefined column

Add a qualifier to deptno and it will display normally.

SQL > select e.deptnoree.salre d.dname from emp e join dept d on (e.deptno=d.deptno) DEPTNO SAL DNAME- 10 2450.00 ACCOUNTING 10 5000.00 ACCOUNTING 10 1300.00 ACCOUNTING 20 2975.00 RESEARCH 20 3000.00 RESEARCH 20 1100.00 RESEARCH 20 800.00 RESEARCH 20 3000.00 RESEARCH 30 1250.00 SALES 30 1500.00 SALES 30 1600.00 SALES 30 950. 00 SALES 30 2850.00 SALES 30 1250.00 SALES14 rows selected

When using using, you do not need to specify a qualifier for the column specified by the selected using in the select statement

When using on, you must add qualifiers to the conditional columns in the conditions of the on statement in the select statement.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.