Posted on 2012-04-09
Last Modified: 2012-04-10

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)
                    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),
                                  - 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.
Question by:neoarwin
  • 6
  • 6
LVL 73

Expert Comment

ID: 37822930
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
LVL 73

Expert Comment

ID: 37822938
'00,' ||  LISTAGG(ev_dt.date_only, ', ') WITHIN GROUP (ORDER BY ev_dt.date_only) as date_only

Author Comment

ID: 37822949
I am using 10g, the listagg won't work in 10g correct?
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

LVL 73

Accepted Solution

sdstuber earned 500 total points
ID: 37823044

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 ',' )
    v_str VARCHAR2(32767);
    IF p_tbl.COUNT > 0
        v_str := p_tbl(1);

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

    RETURN v_str;

Author Comment

ID: 37824155
@sdstuber Thank you very much :)
I will try these and let you know the results.
LVL 73

Expert Comment

ID: 37824164
glad to help.

if you need further assistance, feel free to ask,  if not please remember to close the question appropriately

Author Comment

ID: 37824169
sure, I will try out these solutions tomorrow and will close the question, if this helps to solve my problems.

Thanks for reminding..
LVL 73

Expert Comment

ID: 37824189
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

Author Comment

ID: 37824336
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..
LVL 73

Expert Comment

ID: 37824440
"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.

Author Comment

ID: 37826629
Okay Got it, thank you very much for the explanation. Will get moderators help before going for the 'B' grade.


Author Closing Comment

ID: 37826852
Thank you

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question