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 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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! ./fa/1587/0/
Reproduced in 10.2.0.3 and 9.2.0.8.

Regards
Michel
Re: Order By Decode Variable UNION Issue!! [message #237025 is a reply to message #237021] Fri, 11 May 2007 11:55 Go to previous messageGo to next message
orappnewbie
Messages: 20
Registered: May 2007
Junior Member
select column_list
(select x from a
union
select x from b)
order by decode()

this one is working...

thanks michel....
Re: Order By Decode Variable UNION Issue!! [message #237029 is a reply to message #237025] Fri, 11 May 2007 12:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Check again you don't fall in the same case as I did.
Which version do you use?

Regards
Michel
Re: Order By Decode Variable UNION Issue!! [message #237035 is a reply to message #237029] Fri, 11 May 2007 12:29 Go to previous messageGo to next message
orappnewbie
Messages: 20
Registered: May 2007
Junior Member
mine is oracle application SQL report and it is fine now as per the requirement.... Cool .....Smile
Re: Order By Decode Variable UNION Issue!! [message #237050 is a reply to message #237017] Fri, 11 May 2007 13:44 Go to previous messageGo to next message
flyboy
Messages: 1832
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 #237060 is a reply to message #237050] Fri, 11 May 2007 14:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Of course, silly me, I should use to_char.
Really silly. ./fa/1620/0/

Thanks
Michel
Re: Order By Decode Variable UNION Issue!! [message #237986 is a reply to message #237017] Wed, 16 May 2007 04:58 Go to previous messageGo to next message
alex6
Messages: 1
Registered: May 2007
Location: lille
Junior Member
i've got the same problem and i would like to do something like this :
select ...
union
select ...
order by decode (4, 'LOTD', 1, 'LOT', 2, 'DLUO', 2, 3), 1;

Re: Order By Decode Variable UNION Issue!! [message #238017 is a reply to message #237986] Wed, 16 May 2007 06:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Say it in words what you post is meaningless (in SQL).

Regards
Michel
Re: Order By Decode Variable UNION Issue!! [message #392698 is a reply to message #237017] Wed, 18 March 2009 23:47 Go to previous messageGo to next message
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 Go to previous message
cookiemonster
Messages: 12422
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
ORDER BY <number column>


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.
Previous Topic: Timezone issue with the date field
Next Topic: Insering data into one single table by taking data from 2 different tables
Goto Forum:
  


Current Time: Sat Dec 10 05:17:51 CST 2016

Total time taken to generate the page: 0.25351 seconds