Query attached use wm_concat and this function is not giving the values in correct order.
'00,' || wm_concat (ev_dt.date_only) as date_only
is the one which is not ordering by correctly.
The inner query which gives "ev_dt.date_only" is as below:
SELECT evt_simu_id, sctr_simu_id, prcs_step_id, d.dates dt,
TO_CHAR (d.dates, 'MON') MONTH,
EXTRACT (MONTH FROM d.dates) month_nr,
TO_CHAR (ev.dt, 'dd') date_only, ev.cnt
FROM (SELECT evt_simu_id, sctr_simu_id, prcs_step_id,
init_dt dt, 0 cnt
WHERE prcs_step_id IN (5, 8, 3, 19)
SELECT 0 evt_simu_id, sctr_simu_id, 1 prcs_step_id,
init_dt dt, COUNT (1) cnt
WHERE prcs_step_id = 1
GROUP BY sctr_simu_id, init_dt) ev,
(SELECT TRUNC (SYSDATE, 'mm') + LEVEL - 1 dates
CONNECT BY LEVEL <=
ADD_MONTHS (TRUNC (ADD_MONTHS (SYSDATE, 4),
- TRUNC (SYSDATE, 'mm')
+ 1) d
WHERE d.dates = ev.dt
and sctr_simu_id in(150887, 150008)
ORDER BY ev.dt
And the result set returned by this query is attached in "inner query result set.xls " file.
The main query is attached as well as "Main.sql"
And the result set is attached for main as "main result set. xls"
I want the order of the date to be the same as in teh inner query result set, how to achieve this?