TO_CHAR(v_date,'MON-YYYY') NEW_DATE, I have used for specified format but I wnt to ORDER BY MON bt as its converted into string,I'm not able to do it

Question

SELECT br_code,TO_CHAR(v_date,'MON-YYYY') NEW_DATE,min(bill_amt) new_bill 
from pos_hdr 
GROUP BY br_code,v_date
UNION
SELECT br_code,TO_CHAR(v_date,'MON-YYYY') NEW_DATE,min(bill_amt) AS new_bill 
from pos_prv_hdr 
GROUP BY br_code,v_date 
ORDER BY NEW_DATE;

I want to get the data ordered by Month But as its already converted to String, Its getting ordered by in string format, How can order by it using Month, using the same select query?

I tried many ways, but nothing helped in oracle.

Answer 1

Assuming that you want to group the rows by the br_code and the month then truncate the date to the start of the month and group by that and then format it as a string and order by the truncated date in an outer query:

SELECT br_code,
       TO_CHAR(month,'MON-YYYY') AS new_date,
       new_bill
FROM   (
  SELECT br_code,
         TRUNC(v_date, 'MM') AS month,
         min(bill_amt) AS new_bill
  from   pos_hdr
  GROUP BY
         br_code,
         TRUNC(v_date, 'MM')
UNION
  SELECT br_code,
         TRUNC(v_date, 'MM'),
         min(bill_amt)
  from   pos_prv_hdr
  GROUP BY 
         br_code,
         TRUNC(v_date, 'MM')
)
ORDER BY
       month;

Or, depending on if you want to aggregate the two tables together (rather than keeping them apart and using UNION to discard duplicates) then:

SELECT br_code,
       TO_CHAR(month,'MON-YYYY') AS new_date,
       MIN(bill_amt) AS new_bill
FROM   (
  SELECT br_code,
         TRUNC(v_date, 'MM') AS month,
         bill_amt
  from   pos_hdr
UNION ALL
  SELECT br_code,
         TRUNC(v_date, 'MM') AS month,
         bill_amt
  from   pos_prv_hdr
)  
GROUP BY
       br_code,
       month
ORDER BY
       month;

fiddle