Home » SQL & PL/SQL » SQL & PL/SQL » Order By Decode Variable UNION Issue!!
| Order By Decode Variable UNION Issue!! [message #237017] |
Fri, 11 May 2007 11:12  |
orappnewbie
Messages: 20
Registered: May 2007
|
Junior Member |
|
|
Hi All,
i have a query in which i have to order by a parameter.
but my problem is order by works only for non-union query if i add union to the query it give error.
please following..
SQL> SELECT H.BOL_NUMBER BOL_NUMBER,
2 H.PICKUP_NUMBER,
3 to_char(H.PICKUP_DATE,'DD-MON-YYYY') PICKUP_DATE,
4 RAC.CUSTOMER_NAME CUSTOMER_NAME,
5 L.SALES_ORDER,
6 to_char(MAX(PH.DATE_CONFIRMED),'DD-MON-YYYY HH24:MI:SS') DATE_CONFIRMED
7 FROM APPS.SO_PICKING_HEADERS_ALL PH,
8 SMCOE.SMCOE_BOL_HEADERS H,
9 SMCOE.SMCOE_BOL_LINES L,
10 SO_HEADERS_ALL SHA,
11 SO_ORDER_TYPES_ALL SOTA,
12 APPS.RA_CUSTOMERS RAC,
13 APPS.SO_PICKING_LINES_ALL PL,
14 APPS.SO_PICKING_LINE_DETAILS PLD
15 WHERE H.BOL_HEADER_ID = L.BOL_HEADER_ID
16 AND L.SO_HEADER_ID = SHA.HEADER_ID
17 AND SHA.ORDER_TYPE_ID = SOTA.ORDER_TYPE_ID
18 AND L.CUSTOMER_ID = RAC.CUSTOMER_ID
19 AND H.PICKUP_DATE >= TO_DATE('3-NOV-2006 00:00:00','DD-MON-YYYY HH24:MI:SS')
20 AND H.PICKUP_DATE < TO_DATE('4-NOV-2006 00:00:00','DD-MON-YYYY HH24:MI:SS')
21 AND DECODE(NULL,NULL,'X',
22 RAC.CUSTOMER_NAME) = DECODE(NULL,NULL,'X',
23 NULL)
24 AND DECODE(634307,NULL,1,
25 H.BOL_NUMBER) = DECODE(634307,NULL,1,
26 634307)
27 AND decode('Only SO','Only SO','Z',RAC.CUSTOMER_NAME)=decode('Only SO','Only SO','Z','XXX')
28 AND PH.DATE_CONFIRMED IS NOT NULL
29 AND PH.PICKING_HEADER_ID=PL.PICKING_HEADER_ID
30 AND PL.PICKING_LINE_ID=PLD.PICKING_LINE_ID
31 AND PLD.PICKING_LINE_DETAIL_ID=L.PICKING_LINE_DETAIL_ID
32 AND NOT EXISTS (SELECT 1
33 FROM SO_PICKING_LINE_DETAILS SPLD,WSH_DELIVERIES WD
34 WHERE SPLD.DELIVERY_ID=WD.DELIVERY_ID
35 AND SPLD.PICKING_LINE_DETAIL_ID =L.PICKING_LINE_DETAIL_ID
36 AND WD.STATUS_CODE='OP'
37 )
38 GROUP BY H.BOL_NUMBER ,
39 H.PICKUP_NUMBER,
40 H.PICKUP_DATE ,
41 RAC.CUSTOMER_NAME ,
42 L.SALES_ORDER,
43 L.LINE_NUMBER,
44 L.GROSS_WEIGHT,
45 L.NET_WEIGHT,
46 L.TARE_WEIGHT
47 ORDER BY decode('CUSTOMER','DATE',PICKUP_DATE,'CUSTOMER',CUSTOMER_NAME,'PN',PICKUP_NUMBER,SALES_ORDER);
BOL_NUMBER PICKUP_N PICKUP_DATE CUSTOMER_NAME
---------- -------- ----------- --------------------------------------------------
SALES_ORDER
--------------------------------------------------------------------------------
DATE_CONFIRMED
--------------------
634307 SC22862 03-NOV-2006 A M CASTLE & CO
300014511
03-NOV-2006 19:05:01
634307 SC22862 03-NOV-2006 A M CASTLE & CO
300014542
03-NOV-2006 19:04:55
634307 SC22862 03-NOV-2006 A M CASTLE & CO
300014599
03-NOV-2006 19:04:56
634307 SC22862 03-NOV-2006 A M CASTLE & CO
300014637
03-NOV-2006 19:05:04
634307 SC22862 03-NOV-2006 CORROSION MATERIALS INC
.
.
.
14 rows selected.
SQL> ed
Wrote file afiedt.buf
line 72 truncated.
1 SELECT H.BOL_NUMBER BOL_NUMBER,
2 H.PICKUP_NUMBER,
3 to_char(H.PICKUP_DATE,'DD-MON-YYYY') PICKUP_DATE,
4 RAC.CUSTOMER_NAME CUSTOMER_NAME,
5 L.SALES_ORDER,
6 ' ' DATE_CONFIRMED
7 FROM SMCOE.SMCOE_BOL_HEADERS H,
8 SMCOE.SMCOE_BOL_LINES L,
9 APPS.RA_CUSTOMERS RAC
10 WHERE H.BOL_HEADER_ID = L.BOL_HEADER_ID
11 AND L.CUSTOMER_ID = RAC.CUSTOMER_ID
12 AND H.PICKUP_DATE >= TO_DATE('3-NOV-2006 00:00:00','DD-MON-YYYY HH24:MI:SS')
13 AND H.PICKUP_DATE < TO_DATE('4-NOV-2006 00:00:00','DD-MON-YYYY HH24:MI:SS')
14 AND DECODE(NULL,NULL,'X',
15 RAC.CUSTOMER_NAME) = DECODE(NULL,NULL,'X',
16 NULL)
17 AND DECODE(NULL,NULL,1,
18 H.BOL_NUMBER) = DECODE(NULL,NULL,1,
19 NULL)
20 AND SALES_ORDER NOT LIKE '4%'
21 AND SALES_ORDER NOT LIKE 'PO%'
22 AND SALES_ORDER <> '1'
23 AND L.CUSTOMER_NAME NOT LIKE 'REC%'
24 AND decode('Only SO','Product','Y',RAC.CUSTOMER_NAME)=decode('Only SO','Product','Y','XXX')
25 UNION
26 SELECT H.BOL_NUMBER BOL_NUMBER,
27 H.PICKUP_NUMBER,
28 to_char(H.PICKUP_DATE,'DD-MON-YYYY') PICKUP_DATE,
29 RAC.CUSTOMER_NAME CUSTOMER_NAME,
30 L.SALES_ORDER,
31 to_char(MAX(PH.DATE_CONFIRMED),'DD-MON-YYYY HH24:MI:SS') DATE_CONFIRMED
32 FROM APPS.SO_PICKING_HEADERS_ALL PH,
33 SMCOE.SMCOE_BOL_HEADERS H,
34 SMCOE.SMCOE_BOL_LINES L,
35 SO_HEADERS_ALL SHA,
36 SO_ORDER_TYPES_ALL SOTA,
37 APPS.RA_CUSTOMERS RAC,
38 APPS.SO_PICKING_LINES_ALL PL,
39 APPS.SO_PICKING_LINE_DETAILS PLD
40 WHERE H.BOL_HEADER_ID = L.BOL_HEADER_ID
41 AND L.SO_HEADER_ID = SHA.HEADER_ID
42 AND SHA.ORDER_TYPE_ID = SOTA.ORDER_TYPE_ID
43 AND L.CUSTOMER_ID = RAC.CUSTOMER_ID
44 AND H.PICKUP_DATE >= TO_DATE('3-NOV-2006 00:00:00','DD-MON-YYYY HH24:MI:SS')
45 AND H.PICKUP_DATE < TO_DATE('4-NOV-2006 00:00:00','DD-MON-YYYY HH24:MI:SS')
46 AND DECODE(NULL,NULL,'X',
47 RAC.CUSTOMER_NAME) = DECODE(NULL,NULL,'X',
48 NULL)
49 AND DECODE(634307,NULL,1,
50 H.BOL_NUMBER) = DECODE(634307,NULL,1,
51 634307)
52 AND decode('Only SO','Only SO','Z',RAC.CUSTOMER_NAME)=decode('Only SO','Only SO','Z','XXX')
53 AND PH.DATE_CONFIRMED IS NOT NULL
54 AND PH.PICKING_HEADER_ID=PL.PICKING_HEADER_ID
55 AND PL.PICKING_LINE_ID=PLD.PICKING_LINE_ID
56 AND PLD.PICKING_LINE_DETAIL_ID=L.PICKING_LINE_DETAIL_ID
57 AND NOT EXISTS (SELECT 1
58 FROM SO_PICKING_LINE_DETAILS SPLD,WSH_DELIVERIES WD
59 WHERE SPLD.DELIVERY_ID=WD.DELIVERY_ID
60 AND SPLD.PICKING_LINE_DETAIL_ID =L.PICKING_LINE_DETAIL_ID
61 AND WD.STATUS_CODE='OP'
62 )
63 GROUP BY H.BOL_NUMBER ,
64 H.PICKUP_NUMBER,
65 H.PICKUP_DATE ,
66 RAC.CUSTOMER_NAME ,
67 L.SALES_ORDER,
68 L.LINE_NUMBER,
69 L.GROSS_WEIGHT,
70 L.NET_WEIGHT,
71 L.TARE_WEIGHT
72* ORDER BY decode('CUSTOMER','DATE',PICKUP_DATE,'CUSTOMER',CUSTOMER_NAME,'PN',PICKUP_NUMBER,SALES_ORDER)
SQL> /
OERROR at line 72:
ORA-01785: ORDER BY item must be the number of a SELECT-list expression
SQL> spool off;
[Updated on: Fri, 11 May 2007 11:13] Report message to a moderator |
|
|
|
| Re: Order By Decode Variable UNION Issue!! [message #237021 is a reply to message #237017] |
Fri, 11 May 2007 11:36  |
 |
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Very good! Trying to answer I found a bug:
SQL> var ord varchar2(5)
SQL> exec :ord := 'NO'
PL/SQL procedure successfully completed.
SQL> select * from (
2 select empno no, ename name, 'EMP' type from emp
3 union all
4 select deptno, dname, 'DEPT' from dept
5 )
6 order by decode(:ord,'NO',no,'NAME',name,'TYPE',type)
7 /
NO NAME TYPE
---------- -------------- ----
10 ACCOUNTING DEPT
20 RESEARCH DEPT
30 SALES DEPT
40 OPERATIONS DEPT
7369 SMITH EMP
7499 ALLEN EMP
7521 WARD EMP
7566 JONES EMP
7654 MARTIN EMP
7698 BLAKE EMP
7782 CLARK EMP
7839 KING EMP
7844 TURNER EMP
7900 JAMES EMP
7902 FORD EMP
7934 MILLER EMP
16 rows selected.
SQL> exec :ord := 'NAME'
PL/SQL procedure successfully completed.
SQL> select * from (
2 select empno no, ename name, 'EMP' type from emp
3 union all
4 select deptno, dname, 'DEPT' from dept
5 )
6 order by decode(:ord,'NO',no,'NAME',name,'TYPE',type)
7 /
select * from (
*
ERROR at line 1:
ORA-01722: invalid number
SQL> exec :ord := 'TYPE'
PL/SQL procedure successfully completed.
SQL> select * from (
2 select empno no, ename name, 'EMP' type from emp
3 union all
4 select deptno, dname, 'DEPT' from dept
5 )
6 order by decode(:ord,'NO',no,'NAME',name,'TYPE',type)
7 /
select * from (
*
ERROR at line 1:
ORA-01722: invalid number
SQL> exec :ord := ''
PL/SQL procedure successfully completed.
SQL> select * from (
2 select empno no, ename name, 'EMP' type from emp
3 union all
4 select deptno, dname, 'DEPT' from dept
5 )
6 order by decode(:ord,'NO',no,'NAME',name,'TYPE',type)
7 /
NO NAME TYPE
---------- -------------- ----
7369 SMITH EMP
40 OPERATIONS DEPT
7521 WARD EMP
7566 JONES EMP
7654 MARTIN EMP
7698 BLAKE EMP
7782 CLARK EMP
7839 KING EMP
7844 TURNER EMP
7900 JAMES EMP
7902 FORD EMP
7934 MILLER EMP
10 ACCOUNTING DEPT
20 RESEARCH DEPT
30 SALES DEPT
7499 ALLEN EMP
16 rows selected.
SQL> exec :ord := 'XXXX'
PL/SQL procedure successfully completed.
SQL> select * from (
2 select empno no, ename name, 'EMP' type from emp
3 union all
4 select deptno, dname, 'DEPT' from dept
5 )
6 order by decode(:ord,'NO',no,'NAME',name,'TYPE',type)
7 /
NO NAME TYPE
---------- -------------- ----
7369 SMITH EMP
40 OPERATIONS DEPT
7521 WARD EMP
7566 JONES EMP
7654 MARTIN EMP
7698 BLAKE EMP
7782 CLARK EMP
7839 KING EMP
7844 TURNER EMP
7900 JAMES EMP
7902 FORD EMP
7934 MILLER EMP
10 ACCOUNTING DEPT
20 RESEARCH DEPT
30 SALES DEPT
7499 ALLEN EMP
16 rows selected.
SQL> select * from (
2 select empno no, ename name, 'EMP' type from emp
3 union all
4 select deptno, dname, 'DEPT' from dept
5 )
6 order by decode(:ord,'NO',no,'NAME',name,'TYPE',type, no)
7 /
NO NAME TYPE
---------- -------------- ----
10 ACCOUNTING DEPT
20 RESEARCH DEPT
30 SALES DEPT
40 OPERATIONS DEPT
7369 SMITH EMP
7499 ALLEN EMP
7521 WARD EMP
7566 JONES EMP
7654 MARTIN EMP
7698 BLAKE EMP
7782 CLARK EMP
7839 KING EMP
7844 TURNER EMP
7900 JAMES EMP
7902 FORD EMP
7934 MILLER EMP
16 rows selected.
If value of variable is not the first one or something not in the defined list then Oracle returns an error! 
Reproduced in 10.2.0.3 and 9.2.0.8.
Regards
Michel
|
|
|
|
|
|
|
|
|
|
| Re: Order By Decode Variable UNION Issue!! [message #237050 is a reply to message #237017] |
Fri, 11 May 2007 13:44 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
@Michel: I am afraid Oracle acts correctly as documented here.
| Quote: | Oracle automatically converts expr and each search value to the datatype of the first search value before comparing. Oracle automatically converts the return value to the same datatype as the first result. If the first result has the datatype CHAR or if the first result is null, then Oracle converts the return value to the datatype VARCHAR2.
|
emp.empno and dept.deptno are numbers, aren't they?
|
|
|
|
|
|
|
|
|
|
| Re: Order By Decode Variable UNION Issue!! [message #392698 is a reply to message #237017] |
Wed, 18 March 2009 23:47 |
friendarora
Messages: 24
Registered: February 2007
|
Junior Member |
|
|
We are facing a similar issue.
We have a requirement to do sorting based on the value of the bind parameter.
our SQL is something like
Select empno,empname,hiredate
from emp
order by decode(:param,'O',1,'E',2,'H',3,1);
we are using column position numbers other than the column names because of thier different data types.
Irrespective of the value for the bind variable we get the same result in which there has been no sorting done.
:param:='O'
EMPNO ENAME HIREDATE
----- ------ ---------
7369 SMITH 17-DEC-80
7499 ALLEN 20-FEB-81
7521 WARD 22-FEB-81
7566 JONES 02-APR-81
7654 MARTIN 28-SEP-81
7698 BLAKE 01-MAY-81
7934 MILLER 23-JAN-82
7788 SCOTT 09-DEC-82
7839 KING 17-NOV-81
7844 TURNER 08-SEP-81
7876 ADAMS 12-JAN-83
7900 JAMES 03-DEC-81
7902 FORD 03-DEC-81
7782 CLARK 09-JUN-81
:param:='E'
EMPNO ENAME HIREDATE
----- ------ ---------
7369 SMITH 17-DEC-80
7499 ALLEN 20-FEB-81
7521 WARD 22-FEB-81
7566 JONES 02-APR-81
7654 MARTIN 28-SEP-81
7698 BLAKE 01-MAY-81
7934 MILLER 23-JAN-82
7788 SCOTT 09-DEC-82
7839 KING 17-NOV-81
7844 TURNER 08-SEP-81
7876 ADAMS 12-JAN-83
7900 JAMES 03-DEC-81
7902 FORD 03-DEC-81
7782 CLARK 09-JUN-81
:param:='H'
EMPNO ENAME HIREDATE
----- ------ ---------
7369 SMITH 17-DEC-80
7499 ALLEN 20-FEB-81
7521 WARD 22-FEB-81
7566 JONES 02-APR-81
7654 MARTIN 28-SEP-81
7698 BLAKE 01-MAY-81
7934 MILLER 23-JAN-82
7788 SCOTT 09-DEC-82
7839 KING 17-NOV-81
7844 TURNER 08-SEP-81
7876 ADAMS 12-JAN-83
7900 JAMES 03-DEC-81
7902 FORD 03-DEC-81
7782 CLARK 09-JUN-81
:param:=NULL
EMPNO ENAME HIREDATE
----- ------ ---------
7369 SMITH 17-DEC-80
7499 ALLEN 20-FEB-81
7521 WARD 22-FEB-81
7566 JONES 02-APR-81
7654 MARTIN 28-SEP-81
7698 BLAKE 01-MAY-81
7934 MILLER 23-JAN-82
7788 SCOTT 09-DEC-82
7839 KING 17-NOV-81
7844 TURNER 08-SEP-81
7876 ADAMS 12-JAN-83
7900 JAMES 03-DEC-81
7902 FORD 03-DEC-81
7782 CLARK 09-JUN-81
:param:='XXX'
EMPNO ENAME HIREDATE
----- ------ ---------
7369 SMITH 17-DEC-80
7499 ALLEN 20-FEB-81
7521 WARD 22-FEB-81
7566 JONES 02-APR-81
7654 MARTIN 28-SEP-81
7698 BLAKE 01-MAY-81
7934 MILLER 23-JAN-82
7788 SCOTT 09-DEC-82
7839 KING 17-NOV-81
7844 TURNER 08-SEP-81
7876 ADAMS 12-JAN-83
7900 JAMES 03-DEC-81
7902 FORD 03-DEC-81
7782 CLARK 09-JUN-81
|
|
|
|
| Re: Order By Decode Variable UNION Issue!! [message #392777 is a reply to message #392698] |
Thu, 19 March 2009 04:49 |
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
| friendarora wrote on Thu, 19 March 2009 04:47 |
we are using column position numbers other than the column names because of thier different data types.
|
Then you need to convert them to the same datatype for the sort.
Because:
ORDER BY <function that returns a number>
Is not equivalent to
ORDER BY <number to indicate column from select list>
it IS equivalent to
| friendarora wrote on Thu, 19 March 2009 04:47 |
Irrespective of the value for the bind variable we get the same result in which there has been no sorting done.
|
That's because you're actually ordering by a constant.
You HAVE to refer to the columns by name in a decode for order by to work.
|
|
|
|
Goto Forum:
Current Time: Wed Aug 20 13:02:25 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
.....