SQL-99中,在select中使用distinct关键字后,在order by中出现的字段名称不能超过select子句的范围,如:
SQL> select distinct ename,sal from emp order by deptno;
select distinct ename,sal from emp order by deptno
*
ERROR at line 1:
ORA-01791: not a SELECTed expression
SQL> select distinct ename,sal from emp order by sal;
ENAME SAL
---------- ----------
SMITH 800
JAMES 950
---------------------------------------------
-------------------------------------
select distinct upper(tyt.line_id)
from total_yield_table tyt
where site_id='Beijing'
and product_family='Chen'
and group_level='DPY'
-- and Line_id ='D17CHEN03'
and time_from-15 / 48>trunc(sysdate-15 / 48)
and time_from<=sysdate
order by upper(line_id)
SQL> select distinct ename,sal from emp order by deptno;
select distinct ename,sal from emp order by deptno
*
ERROR at line 1:
ORA-01791: not a SELECTed expression
SQL> select distinct ename,sal from emp order by sal;
ENAME SAL
---------- ----------
SMITH 800
JAMES 950
---------------------------------------------
-------------------------------------
select distinct upper(tyt.line_id)
from total_yield_table tyt
where site_id='Beijing'
and product_family='Chen'
and group_level='DPY'
-- and Line_id ='D17CHEN03'
and time_from-15 / 48>trunc(sysdate-15 / 48)
and time_from<=sysdate
order by upper(line_id)