WM_CONCAT usage

Hi,

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
                      FROM evt
                     WHERE prcs_step_id IN (5, 8, 3, 19)
                    UNION
                    SELECT   0 evt_simu_id, sctr_simu_id, 1 prcs_step_id,
                             init_dt dt, COUNT (1) cnt
                        FROM evt
                       WHERE prcs_step_id = 1
                    GROUP BY sctr_simu_id, init_dt) ev,
                   (SELECT     TRUNC (SYSDATE, 'mm') + LEVEL - 1 dates
                          FROM DUAL
                    CONNECT BY LEVEL <=
                                    ADD_MONTHS (TRUNC (ADD_MONTHS (SYSDATE, 4),
                                                       'mm'
                                                      ),
                                                1
                                               )
                                  - 1
                                  - 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?

Please help.
Query-files.zip
neoarwinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
wm_concat is not unsupported  
you shouldn't use it


if you're using 11g then listagg is appropriate.

if 10g then use collect with a function to iterate through the collection and build the string or write your own user defined aggregate  or use xml aggregation

if 9i write your own user defined aggregate or use xml aggregation

http://www.experts-exchange.com/A_9391.html
0
sdstuberCommented:
'00,' ||  LISTAGG(ev_dt.date_only, ', ') WITHIN GROUP (ORDER BY ev_dt.date_only) as date_only
0
neoarwinAuthor Commented:
I am using 10g, the listagg won't work in 10g correct?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sdstuberCommented:
correct.

for 10g you can use something like this...


RTRIM(EXTRACT(XMLAGG(XMLELEMENT("x", ev_dt.date_only || ',') order by ev_dt.date_only), '/x/text()').getstringval(),',')


tbl2str(CAST(COLLECT(ev_dt.date_only ORDER BY ev_dt.date_only) AS vcarray))

concat_agg(ev_dt.date_only) over(ORDER BY ev_dt.date_only ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)


tbl2str and vcarray would have to be created,  concat_agg is in the article linked above,  xmlagg is builtin



CREATE OR REPLACE TYPE vcarray as table of varchar2(4000);

CREATE OR REPLACE FUNCTION tbl2str(p_tbl IN vcarray, p_delimiter IN VARCHAR2 DEFAULT ',' )
    RETURN VARCHAR2
    DETERMINISTIC
IS
    v_str VARCHAR2(32767);
BEGIN
    IF p_tbl.COUNT > 0
    THEN
        v_str := p_tbl(1);

        FOR i IN 2 .. p_tbl.COUNT
        LOOP
            v_str := v_str || p_delimiter || p_tbl(i);
        END LOOP;
    END IF;

    RETURN v_str;
END;
/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
neoarwinAuthor Commented:
@sdstuber Thank you very much :)
I will try these and let you know the results.
0
sdstuberCommented:
glad to help.

if you need further assistance, feel free to ask,  if not please remember to close the question appropriately
0
neoarwinAuthor Commented:
sure, I will try out these solutions tomorrow and will close the question, if this helps to solve my problems.

Thanks for reminding..
0
sdstuberCommented:
no hurry,
it was more a reminder to not just close, but to close appropriately
you have awarded penalty grades inappropriately on multiple occasions, requiring Moderator and/or Zone Advisor intervention to correct them
0
neoarwinAuthor Commented:
I don't have any idea of what you are talking about.
Anyway I will keep a watch on inappropriate stuffs.
will have to learn about how this site works..
0
sdstuberCommented:
"B" grades are penalties,  most of your closed questions were graded with a "B".

"A" is the standard.  You are, of course, allowed to give penalty grades when warranted; but if you do, you are supposed to give an explanation for why the answers were deficient.

If there are answers posted that you have not responded to, either requesting additional information or explaining why the answers didn't work, then a penalty grade is always inappropriate.


If you're ever unsure how to close, you can always ask within the question for suggestions, or click the Request Attention link to have a Moderator assist you.
0
neoarwinAuthor Commented:
Okay Got it, thank you very much for the explanation. Will get moderators help before going for the 'B' grade.

Regards.
0
neoarwinAuthor Commented:
Thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.