The query likes following code. It will not work if "ORDER BY" is in this query. SELECT ... FROM ... WHERE ... ORDER BY ... -- Error happen, if "ORDER BY" added UNION SELECT ... FROM ... WHERE ... ORDER BY ... ; -- Error happen, if "ORDER BY" addedAny ideals ???
some advises, 1. if you are using previous version before Oracle 8i, in SQL statement with UNION or UNION ALL operation, you can only add 'order by' cause in the last select statement, as shown below, SELECT ... FROM ... WHERE ... UNION SELECT ... FROM ... WHERE ... ORDER BY 2, 1, 3; -- the numbers stand for column2/1/3 in selecting list. in this example, the ORDER BY cause will function for the whole statement.2. please consider the difference between UNION and UNION ALL. actually, UNION operation will automatically sort the query result by columns in selecting list, and will seriously slow down the query operation. if no special requirement on distinct result, please use UNION ALL instead. this will bring you much high performance.
Yes, there is the filed (orader by字段) in both select query. Here is my code. SELECT DISTINCT C.company_id, C.company_nm insurer_nm, ... FROM IQUASR.INSURER I, COREN.ADDRESS AD, COREN.COMPANY C, ... WHERE I.work_area_id = in_numWorkAreaID AND C.company_id = I.company_id ... ORDER BY APPER(C.company_nm) ASC -- error line
UNION
SELECT DISTINCT C.company_id, C.company_nm insurer_nm, ... FROM IQUASR.INSURER I, COREN.ADDRESS_PHONE_FROM_NAIC AD, COREN.COMPANY C, ... WHERE I.work_area_id = in_numWorkAreaID AND C.company_id = I.company_id ... ORDER BY APPER(C.company_nm) ASC; -- error line
FROM ...
WHERE ...
ORDER BY ... -- Error happen, if "ORDER BY" added
UNION
SELECT ...
FROM ...
WHERE ...
ORDER BY ... ; -- Error happen, if "ORDER BY" addedAny ideals ???
把visible设置为false
1. if you are using previous version before Oracle 8i, in SQL statement with UNION or UNION ALL operation, you can only add 'order by' cause in the last select statement, as shown below,
SELECT ...
FROM ...
WHERE ...
UNION
SELECT ...
FROM ...
WHERE ... ORDER BY 2, 1, 3; -- the numbers stand for column2/1/3 in selecting list.
in this example, the ORDER BY cause will function for the whole statement.2. please consider the difference between UNION and UNION ALL. actually, UNION operation will automatically sort the query result by columns in selecting list, and will seriously slow down the query operation. if no special requirement on distinct result, please use UNION ALL instead. this will bring you much high performance.
C.company_id,
C.company_nm insurer_nm,
...
FROM IQUASR.INSURER I,
COREN.ADDRESS AD,
COREN.COMPANY C,
...
WHERE I.work_area_id = in_numWorkAreaID
AND C.company_id = I.company_id
...
ORDER BY APPER(C.company_nm) ASC -- error line
UNION
SELECT DISTINCT
C.company_id,
C.company_nm insurer_nm,
...
FROM IQUASR.INSURER I,
COREN.ADDRESS_PHONE_FROM_NAIC AD,
COREN.COMPANY C,
...
WHERE I.work_area_id = in_numWorkAreaID
AND C.company_id = I.company_id
...
ORDER BY APPER(C.company_nm) ASC; -- error line