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 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
shahzad-ul-hasan
Messages: 625 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.
please help me to sort out.
|
|
|
|
|
|
Re: order by union query [message #687850 is a reply to message #687849] |
Wed, 28 June 2023 07:55 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 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.
When it says that it must be the number of a select list expression, it is not referring to the datatype.
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
This is only because you are applying the lpad function to the column.
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.
Also, as previously mentioned by Michel, you do not need distinct with union.
[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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Solomon Yakobson
Messages: 3280 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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Solomon Yakobson
Messages: 3280 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>
We have to do something like this to enforce distinct:
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>
SY.
|
|
|
Re: order by union query [message #687902 is a reply to message #687852] |
Tue, 18 July 2023 12:55 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.gravatar.com/avatar/5db0aa55eb576006ce1bb00eee2e35d9?s=64&d=mm&r=g) |
mathguy
Messages: 108 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(....);
Of course, the LPAD call has to be syntactically correct - as already pointed out, the code you posted will throw a different error related to 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 Sep 07 19:06:34 CDT 2024
|