各位遇到个问题
SELECT
DWR.ENTRY_NO
FROM
WEEKLYREPORT DWR
WHERE
DWR.SERVICE_CODE ='0001'
AND DWR.SERVICE_YEAR ='2007'
AND DWR.BRANCH_CODE ='01'
UNION ALL SELECT
DWR.ENTRY_NO
FROM
WEEKLYREPORT2 DWR
WHERE
DWR.SERVICE_CODE ='0001'
AND DWR.SERVICE_YEAR ='2007'
AND DWR.BRANCH_CODE ='01'
ORDER BY DWR.WEEKLY_NO
这句SQL在ORDER BY 这里有点问题和解?各位帮帮我啊
我是 ORDER BY DWR.WEEKLY_NO 哦。。
各位路过的大虾帮帮小妹 ,那个号的分给完了。。换个。。散分ING
SELECT
DWR.ENTRY_NO
FROM
WEEKLYREPORT DWR
WHERE
DWR.SERVICE_CODE ='0001'
AND DWR.SERVICE_YEAR ='2007'
AND DWR.BRANCH_CODE ='01'
UNION ALL SELECT
DWR.ENTRY_NO
FROM
WEEKLYREPORT2 DWR
WHERE
DWR.SERVICE_CODE ='0001'
AND DWR.SERVICE_YEAR ='2007'
AND DWR.BRANCH_CODE ='01'
ORDER BY DWR.WEEKLY_NO
这句SQL在ORDER BY 这里有点问题和解?各位帮帮我啊
我是 ORDER BY DWR.WEEKLY_NO 哦。。
各位路过的大虾帮帮小妹 ,那个号的分给完了。。换个。。散分ING
SELECT
DWR.ENTRY_NO
FROM
WEEKLYREPORT DWR
WHERE
DWR.SERVICE_CODE ='0001'
AND DWR.SERVICE_YEAR ='2007'
AND DWR.BRANCH_CODE ='01'
UNION ALL SELECT
DWR.ENTRY_NO
FROM
WEEKLYREPORT2 DWR
WHERE
DWR.SERVICE_CODE ='0001'
AND DWR.SERVICE_YEAR ='2007'
AND DWR.BRANCH_CODE ='01') test
ORDER BY test.WEEKLY_NO
DWR.ENTRY_NO
FROM
WEEKLYREPORT DWR
WHERE
DWR.SERVICE_CODE ='0001'
AND DWR.SERVICE_YEAR ='2007'
AND DWR.BRANCH_CODE ='01'
UNION ALL
SELECT A.ENTRY_NO
FROM
(
SELECT
DWR.ENTRY_NO
FROM
WEEKLYREPORT2 DWR
WHERE
DWR.SERVICE_CODE ='0001'
AND DWR.SERVICE_YEAR ='2007'
AND DWR.BRANCH_CODE ='01'
ORDER BY DWR.WEEKLY_NO
) A
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
F1 VARCHAR2(10) Y
F2 VARCHAR2(10) Y SQL>
SQL> select * from test union all select * from test order by f1;select * from test union all select * from test order by f1ORA-00904: "F1": 无效的标识符
SQL> select * from test union all select * from test order by 1;F1 F2
---------- ----------
001 002
001 002
001 002
001 002
002 002
002 004
002 004
002 002
004 008
004 008
234 002
234 00212 rows selected 这是个bug,可以按如下方法变通解决一下.Subject: ORA-00904 doing Order by on a column in a Union
Doc ID: Note:281487.1 Type: PROBLEM
Last Revision Date: 14-DEC-2004 Status: PUBLISHED
The information in this article applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.5
This problem can occur on any platform.Errors
ORA-00904Symptoms
Order by on a column in a union fails with ORA-00904Testcase
============
create table a (a1 int);
insert into a values (1);
create table b (b1) as select * from a ; select * from a union select * from b order by a1;
ORA-00904: "A1": invalid identifierAlthough the result column is named a1, we can't order by a1.
Cause
This is a Bug - 3122179 (not available externally) Abstract: (SELECT * FROM A UNION SELECT * FROM B) ORDER BY A1;
Fix
Many workarounds are available.
One of them would be using "order by 1" ;eg. select * from a union select * from b order by 1 ;
References