Hi,
I would like to sort the data based on the date column hence was trying to add "ORDER BY to_date(transaction_dates) & ORDER BY to_date(1) ; but I am getting following error while execution of the SQL query
ORA-01785: ORDER BY item must be the number of a SELECT-list expression
SELECT To_char(trx_date, 'MM/DD/YYYY') transaction_dates
FROM ra_customer_trx_all
UNION
SELECT To_char(receipt_date, 'MM/DD/YYYY') transaction_dates
FROM ar_cash_receipts_all
ORDER BY To_date(transaction_dates)
--order by to_date(1)
I can resolve the issue by adding subquery as mentioned below but i don't want to use subquery as we have large amount of data. so i was trying to avoid to use subquery and looking for suggestions.
select * from (
select to_char(trx_date,'MM/DD/YYYY') transaction_dates from ra_customer_trx_all
union
select to_char(receipt_date,'MM/DD/YYYY') transaction_dates from ar_cash_receipts_all)
order by to_date(transaction_dates)
--order by to_date(1)