Home » SQL & PL/SQL » SQL & PL/SQL » order by union query (Form 6i, Database10g)
| order by union query [message #687846] |
Wed, 28 June 2023 01:07  |
shahzad-ul-hasan
Messages: 615
Registered: August 2002
|
Senior Member |
|
|
i am have two schema (sis1 is first and gtt is the 2nd) the column (adm_no) datatype is varchar2(20).i am using this query.
select distinct s.adm_no,s.stuid,' '||s.name name,s.bayform,' '||f.f_name f_name,f.fcnic,f.caste,f.mobile,s.status,s.coment,f.fam_id,f.profess,s.Doa,s.Dob,f.addres,s.class,
s.adm_class, s.Leav_date, s.coment
FROM student s,family f
where f.fam_id=s.fam_id
and adm_no like 'P%'
union
select distinct gtt.s.adm_no,gtt.s.stuid, ' '||gtt.s.name name,gtt.s.bayform, ' '||gtt.f.f_name f_name,gtt.f.fcnic, gtt.f.caste,gtt.f.mobile,gtt.s.status,gtt.s.coment,gtt.f.fam_id,
gtt.f.profess, gtt.s.Doa, gtt.s.Dob, gtt.f.addres, gtt.s.class,
gtt.s.adm_class, gtt.s.Leav_date, gtt.s.coment
FROM gtt.student s,gtt.family f
where gtt.f.fam_id=gtt.s.fam_id
and gtt.s.adm_no like 'P%'
order by lpad(adm_no) desc --ORA-01785 >>>>>>> Receives this error
----ORA-01785 >>>>>>> Receives this error order by item must bbe number.
|
|
|
|
|
|
|
|
|
|
| Re: order by union query [message #687850 is a reply to message #687849] |
Wed, 28 June 2023 07:55  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
There are multiple problems here.
lpad(adm_no) is not valid syntax, as shown below.
-- wrong:
SCOTT@orcl_12.1.0.2.0> select lpad(dname) from dept
2 /
select lpad(dname) from dept
*
ERROR at line 1:
ORA-00938: not enough arguments for function
-- right:
SCOTT@orcl_12.1.0.2.0> select lpad(dname,20) from dept
2 /
LPAD(DNAME,20)
--------------------------------------------------------------------------------
ACCOUNTING
RESEARCH
SALES
OPERATIONS
4 rows selected.
It is referring to the position in the select list.
You must order by 1 for the first column or 2 for the second column, and so forth, as shown below.
-- wrong:
SCOTT@orcl_12.1.0.2.0> select distinct lpad(dname,20) dname, loc from dept where deptno = 10
2 union
3 select distinct lpad(dname,20) dname, loc from dept where deptno = 20
4 order by lpad(dname,20)
5 /
order by lpad(dname,20)
*
ERROR at line 4:
ORA-01785: ORDER BY item must be the number of a SELECT-list expression
-- right:
SCOTT@orcl_12.1.0.2.0> select distinct lpad(dname,20) dname, loc from dept where deptno = 10
2 union
3 select distinct lpad(dname,20) dname, loc from dept where deptno = 20
4 order by 1
5 /
DNAME LOC
-------------------- -------------
RESEARCH DALLAS
ACCOUNTING NEW YORK
2 rows selected.
SCOTT@orcl_12.1.0.2.0> select distinct lpad(dname,20) dname, loc from dept where deptno = 10
2 union
3 select distinct lpad(dname,20) dname, loc from dept where deptno = 20
4 order by 2
5 /
DNAME LOC
-------------------- -------------
RESEARCH DALLAS
ACCOUNTING NEW YORK
2 rows selected
Without lpad there is no problem as shown below.
SCOTT@orcl_12.1.0.2.0> select distinct dname, loc from dept where deptno = 10
2 union
3 select distinct dname, loc from dept where deptno = 20
4 order by dname
5 /
DNAME LOC
-------------------- -------------
ACCOUNTING NEW YORK
RESEARCH DALLAS
2 rows selected.
[Updated on: Wed, 28 June 2023 12:15] Report message to a moderator |
|
|
|
| Re: order by union query [message #687851 is a reply to message #687850] |
Wed, 28 June 2023 11:51 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Barbara, just to clarify, DISTINCT in UNION isn't needed in 99.9% of cases. However if both parts of the UNION return very large volume (in relation to available temp space) then not just our session but other sessions too might run out of temp space when our session does sorting and if DISTINCT applied to one or both parts of the UNION reduces that volume so temp space isn't exceeded.
SY.
|
|
|
|
| Re: order by union query [message #687852 is a reply to message #687851] |
Wed, 28 June 2023 12:16 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
In addition, optimizer is smart enough to discard distinct in unionized query:
SQL> explain plan for
2 select distinct ename from emp union select distinct dname from dept;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
Plan hash value: 2375100902
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18 | 124 | 8 (25)| 00:00:01 |
| 1 | SORT UNIQUE | | 18 | 124 | 8 (25)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | TABLE ACCESS FULL| EMP | 14 | 84 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| DEPT | 4 | 40 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
11 rows selected.
SQL>
SQL> explain plan for
2 with u1 as (select /*+ materialize */ distinct ename from emp),
3 t2 as (select /*+ materialize */ distinct dname from dept)
4 select * from u1 union select * from t2;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
Plan hash value: 770236521
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18 | 134 | 6 (34)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6613_8EA5904 | | | | |
| 3 | HASH UNIQUE | | 14 | 84 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL | EMP | 14 | 84 | 3 (0)| 00:00:01 |
| 5 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6614_8EA5904 | | | | |
| 6 | HASH UNIQUE | | 4 | 40 | 4 (25)| 00:00:01 |
| 7 | TABLE ACCESS FULL | DEPT | 4 | 40 | 3 (0)| 00:00:01 |
| 8 | SORT UNIQUE | | 18 | 134 | 6 (34)| 00:00:01 |
| 9 | UNION-ALL | | | | | |
| 10 | VIEW | | 14 | 98 | 2 (0)| 00:00:01 |
| 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6613_8EA5904 | 14 | 84 | 2 (0)| 00:00:01 |
| 12 | VIEW | | 4 | 36 | 2 (0)| 00:00:01 |
| 13 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6614_8EA5904 | 4 | 40 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------
20 rows selected.
SQL>
|
|
|
|
| Re: order by union query [message #687902 is a reply to message #687852] |
Tue, 18 July 2023 12:55 |
 |
mathguy
Messages: 107
Registered: January 2023
|
Senior Member |
|
|
The workaround is to use a subquery - outer query structure:
select *
from (
your UNION query here, WITHOUT the ORDER BY clause
)
order by lpad(....);
Question though - why do you need the LPAD? Is ADM_NO in fact a numeric column, but stored in the wrong data type (VARCHAR2)? If so, why is that? And, in any case, if that is so, why LPAD and not TO_NUMBER? I can't think of another meaningful situation where you would need to LPAD before ordering.
|
|
|
|
Goto Forum:
Current Time: Sat Apr 27 11:44:59 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
