oracle order by date clause in apex tabular form

Question

I have an oracle apex tabular form with following region query

select APEX_ITEM.DATE_POPUP2(1,sysdate,'DD-MM-YYYY') DIV_DATE, 
       APEX_ITEM.TEXT(2,null ,10,15,'style="width:100px;text-align:right"') CREDIT 
   from dual 
                             union 
select APEX_ITEM.DATE_POPUP2(1,DIV_DATE,'DD-MM-YYYY') DIV_DATE,
       APEX_ITEM.TEXT(2,CREDIT,10,15,'style="width:100px;text-align:right"') CREDIT
   from DIVIDENT_REG
   where code=:MCODE and memno=:MEMNO and brcode=:BRCODE
   order by  DIV_DATE desc

I want the result in discending order of column DIV_DATE. But the above order by clause does not work. So I used the code

order by to_date(DIV_DATE) desc

But it shows an error

ORA-01785: ORDER BY item must be the number of a SELECT-list expression

Does anyone have an idea to solve this problem?

EDIT : I can not choose

select div_date,credit from(    
select APEX_ITEM.DATE_POPUP2(1,sysdate,'DD-MM-YYYY') DIV_DATE, 
       APEX_ITEM.TEXT(2,null ,10,15,'style="width:100px;text-align:right"') CREDIT 
   from dual 
                             union 
select APEX_ITEM.DATE_POPUP2(1,DIV_DATE,'DD-MM-YYYY') DIV_DATE,
       APEX_ITEM.TEXT(2,CREDIT,10,15,'style="width:100px;text-align:right"') CREDIT
   from DIVIDENT_REG
   where code=:MCODE and memno=:MEMNO and brcode=:BRCODE
   order by  DIV_DATE desc    )

since it will drop properties of my apex_item

Answer 1

Would any of these options help?

First, ORDER BY 1 DESC, where "1" represents the first column returned by a query:

select APEX_ITEM.DATE_POPUP2(1,sysdate,'DD-MM-YYYY') DIV_DATE, 
       APEX_ITEM.TEXT(2,null ,10,15,'style="width:100px;text-align:right"') CREDIT 
from dual 
union 
select APEX_ITEM.DATE_POPUP2(1,DIV_DATE,'DD-MM-YYYY') DIV_DATE,
       APEX_ITEM.TEXT(2,CREDIT,10,15,'style="width:100px;text-align:right"') CREDIT
from DIVIDENT_REG
where code=:MCODE and memno=:MEMNO and brcode=:BRCODE
order by 1 desc;

Or, using your "original" query as an inline view, and then "normally" order by DIV_DATE:

select div_date, credit
from (select APEX_ITEM.DATE_POPUP2(1,sysdate,'DD-MM-YYYY') DIV_DATE, 
             APEX_ITEM.TEXT(2,null ,10,15,'style="width:100px;text-align:right"') CREDIT 
      from dual 
      union 
      select APEX_ITEM.DATE_POPUP2(1,DIV_DATE,'DD-MM-YYYY') DIV_DATE,
             APEX_ITEM.TEXT(2,CREDIT,10,15,'style="width:100px;text-align:right"') CREDIT
      from DIVIDENT_REG
      where code=:MCODE and memno=:MEMNO and brcode=:BRCODE
     )
order by div_date desc;

Answer 2

Try this:

SELECT   TB.*
    FROM (SELECT APEX_ITEM.DATE_POPUP2 (1,
                                        SYSDATE,
                                        'DD-MM-YYYY') DIV_DATE,
                 APEX_ITEM.TEXT (2,
                                 NULL,
                                 10,
                                 15,
                                 'style="width:100px;text-align:right"')
                                                                       CREDIT
            FROM DUAL
          UNION
          SELECT APEX_ITEM.DATE_POPUP2 (1,
                                        DIV_DATE,
                                        'DD-MM-YYYY') DIV_DATE,
                 APEX_ITEM.TEXT (2,
                                 CREDIT,
                                 10,
                                 15,
                                 'style="width:100px;text-align:right"')
                                                                       CREDIT
            FROM DIVIDENT_REG
           WHERE CODE = :MCODE
                 AND MEMNO = :MEMNO
                 AND BRCODE = :BRCODE) TB
ORDER BY TB.DIV_DATE DESC