万恶之源-SQL

本篇内容针对以下三个报错进行解析

ORA-01790: 表达式必须具有与对应表达式相同的数据类型

出现此报错的原因是SQL语句中出现了UNION或UNION ALL关键字

需要注意相同名称字段的数据类型一定要相同

举个例子： 我第一个查询结果集是empno和sal  第二个结果集是empno和job，sal和job是两个不同的数据类型 那么就会出现如下报错。

SYS@ prod>select empno,sal s from scott.emp where deptno=10  
  2  union
  3  select empno,job j from scott.emp  where deptno=30;
select empno,sal s from scott.emp where deptno=10
             *
第 1 行出现错误:
ORA-01790: 表达式必须具有与对应表达式相同的数据类型

 

其他类似报错情况就是结果集中的字段没有一一对应

解决方法：

SYS@ prod>select empno,sal ,job from scott.emp where deptno=10   union  select empno,job,sal from scott.emp  where deptno=30 order by sal;
select empno,sal ,job from scott.emp where deptno=10   union  select empno,job,sal from scott.emp  where deptno=30 order by sal
             *
第 1 行出现错误:
ORA-01790: 表达式必须具有与对应表达式相同的数据类型


SYS@ prod>select empno,job,sal  from scott.emp where deptno=10   union  select empno,job,sal from scott.emp  where deptno=30 order by sal;

     EMPNO JOB              SAL
---------- --------- ----------
      7900 CLERK            950
      7521 SALESMAN        1250
      7654 SALESMAN        1250
      7934 CLERK           1300
      7844 SALESMAN        1500
      7499 SALESMAN        1600
      7782 MANAGER         2450
      7698 MANAGER         2850
      7839 PRESIDENT       5000

已选择9行。

SYS@ prod>

 

ORA-00904: "B": 标识符无效

同样的使用union和union all关键字

select empno,sal s from scott.emp where deptno=10   union  select empno b,sal from scott.emp  where deptno=30 order by b
                                                                                                                       *
第 1 行出现错误:
ORA-00904: "B": 标识符无效

SYS@ prod>select empno,sal s from scott.emp where deptno=10   union  select empno b,sal from scott.emp  where deptno=30 order by s;

     EMPNO          S
---------- ----------
      7900        950
      7521       1250
      7654       1250
      7934       1300
      7844       1500
      7499       1600
      7782       2450
      7698       2850
      7839       5000

已选择9行。

SYS@ prod>select empno,sal s from scott.emp where deptno=10   union  select empno b,sal from scott.emp  where deptno=30 order by empno;

     EMPNO          S
---------- ----------
      7499       1600
      7521       1250
      7654       1250
      7698       2850
      7782       2450
      7839       5000
      7844       1500
      7900        950
      7934       1300

已选择9行。

SYS@ prod>select empno,sal s from scott.emp where deptno=10   union  select empno b,sal from scott.emp  where deptno=30 order by 2;

     EMPNO          S
---------- ----------
      7900        950
      7521       1250
      7654       1250
      7934       1300
      7844       1500
      7499       1600
      7782       2450
      7698       2850
      7839       5000

已选择9行。

SYS@ prod>select empno,sal s from scott.emp where deptno=10   union  select empno b,sal from scott.emp  where deptno=30 order by 1;

     EMPNO          S
---------- ----------
      7499       1600
      7521       1250
      7654       1250
      7698       2850
      7782       2450
      7839       5000
      7844       1500
      7900        950
      7934       1300

已选择9行。

SYS@ prod>select empno,sal s from scott.emp where deptno=10   union  select empno b,sal from scott.emp  where deptno=30 order by 2,empno;

     EMPNO          S
---------- ----------
      7900        950
      7521       1250
      7654       1250
      7934       1300
      7844       1500
      7499       1600
      7782       2450
      7698       2850
      7839       5000

已选择9行。

SYS@ prod>select empno,sal s from scott.emp where deptno=10   union  select empno b,sal from scott.emp  where deptno=30 order by b,s;
select empno,sal s from scott.emp where deptno=10   union  select empno b,sal from scott.emp  where deptno=30 order by b,s
                                                                                                                       *
第 1 行出现错误:
ORA-00904: "B": 标识符无效


SYS@ prod>select empno,sal s from scott.emp where deptno=10   union  select empno b,sal from scott.emp  where deptno=30 order by 2,sal;
select empno,sal s from scott.emp where deptno=10   union  select empno b,sal from scott.emp  where deptno=30 order by 2,sal
                                                                                                                         *
第 1 行出现错误:
ORA-00904: "SAL": 标识符无效

出现报错的原因就是order by语句后面的列必须是第一个SQL中所包含的列，OCP考试中涉及此题，上图请手动自己执行一次即可得到答案

 

ORA-01789: 查询块具有不正确的结果列数

第一个SQL与第二个SQL所包含的列数不相等

SYS@ prod>select empno,sal  from scott.emp where deptno=10   union  select empno,job,sal from scott.emp  where deptno=30 order by 2,empno;
select empno,sal  from scott.emp where deptno=10   union  select empno,job,sal from scott.emp  where deptno=30 order by 2,empno
*
第 1 行出现错误:
ORA-01789: 查询块具有不正确的结果列数

 

另说明一下 只要UNION两个子查询的列数和字段类型对应  就可以正常执行：

SYS@ prod>select empno,sal  from scott.emp where deptno=10   union  select empno,COMM  from scott.emp  where deptno=30 order by 2,empno;

     EMPNO        SAL
---------- ----------
      7844          0
      7499        300
      7521        500
      7934       1300
      7654       1400
      7782       2450
      7839       5000
      7698
      7900

已选择9行。

SYS@ prod>select empno,sal  from scott.emp where deptno=10   union all select empno,COMM  from scott.emp  where deptno=30 order by 2,empno;

     EMPNO        SAL
---------- ----------
      7844          0
      7499        300
      7521        500
      7934       1300
      7654       1400
      7782       2450
      7839       5000
      7698
      7900

已选择9行。

OWNER：Jrojyun

DATE:2021-03-21