This discussion is archived
13 Replies Latest reply: Apr 2, 2013 8:51 AM by 651776 RSS

Need your Help for writing complex query !!!

user3558544 Newbie
Currently Being Moderated
Kindly give me your suggestion to write a query for given situation ..

suupose there is 10 distributor in circle . and also in each distributor , there is 10 FOS under each disributor ..and also in each fos , there is number retailers...

Kindly suggest how to write query ,it's look like ..(may possible through XML ...

Distributor1, FOS1, rt_count ,FOS2 , rt_count , FOS3, rt_count
Distributor2, FOS1, rt_count ,FOS2 , rt_count , FOS3, rt_count
...
...
...
8751090151,8751090147:10 ,8751090148:25 ,8751090149:11 ....etc


the below is data ..

date_crt     dist_msisdn     f os_msisdn     rt_count
26-MAR-13     7399998263     7399998263     1
26-MAR-13     8751090151     8751090147     10
26-MAR-13     8751090151     8751090148     25
26-MAR-13     8751090151     8751090149     11
26-MAR-13     8751090151     8751090151     1
26-MAR-13     8751090151     8751090152     40
26-MAR-13     8751090151     8751090326     11
26-MAR-13     8751090151     8751090327     70
26-MAR-13     8751090151     8751090328     29
26-MAR-13     8751090151     8751090335     1
26-MAR-13     8751090151     9577162731     8
26-MAR-13     8751090151     9613381589     38
26-MAR-13     8751090151     9613381790     14
26-MAR-13     8751090151     9613381969     13
26-MAR-13     8751090151     9613385207     42
26-MAR-13     8751090151     9613385284     12
26-MAR-13     8751090151     9613386523     52
26-MAR-13     8751090151     9613389220     91
26-MAR-13     8751090151     9613530655     1

Edited by: user3558544 on Apr 1, 2013 5:36 AM

Edited by: user3558544 on Apr 1, 2013 7:09 AM
  • 1. Re: Need your Help for writing complex query !!!
    jeneesh Guru
    Currently Being Moderated
    Check for PIVOT

    Raed FAQ:

    {message:id=9360005}

    {message:id=9360002}
  • 2. Re: Need your Help for writing complex query !!!
    651776 Newbie
    Currently Being Moderated
    You can make a dynamic sql, I leave you two examples, that I have used a few years ago.

    Example 1:
    SELECT deptno, SUBSTR (SYS_CONNECT_BY_PATH (lname, ','), 2) name_list
    FROM (SELECT lname, deptno, COUNT (*) OVER (PARTITION BY deptno) cnt,
    ROW_NUMBER () OVER (PARTITION BY deptno ORDER BY lname)
    seq
    FROM (SELECT deptno,
    lname
    FROM employees) emp
    WHERE deptno IS NOT NULL)
    WHERE seq = cnt
    START WITH seq = 1
    CONNECT BY PRIOR seq + 1 = seq AND PRIOR deptno = deptno;

    Example 2:
    create table tmp_1 (a number, b varchar2(10));
    insert into tmp_1 values (1, 'a');
    insert into tmp_1 values (2, 'a');
    insert into tmp_1 values (2, 'b');
    insert into tmp_1 values (3, 'x');
    insert into tmp_1 values (3, 'y');
    insert into tmp_1 values (4, 'a');
    insert into tmp_1 values (4, 'b');
    insert into tmp_1 values (4, 'c');

    select * from tmp_1;

    Resultado:
    A B
    ---------
    1 a
    2 a
    2 b
    3 x
    3 y
    4 a
    4 b
    4 c

    -- A partir de Oracle 9i
    select a, rtrim (xmlagg (xmlelement (e, b || ',' ) order by b).extract ('//text()'), ',') combinatoria_b
    from tmp_1
    group by a

    Resultado:
    A COMBINATORIA_B
    ----------------------
    1 a
    2 a,b
    3 x,y
    4 a,b,c
  • 3. Re: Need your Help for writing complex query !!!
    user3558544 Newbie
    Currently Being Moderated
    But my query result looks like

    Distributor1, FOS1 :count(RETAILER under FOS1) ,FOS2 :count(RETAILER under FOS2) , FOS3 :count(RETAILER under FOS3)
    like ..

    8751090151,8751090147:10 ,8751090148:25 ,8751090149:11 ....so on....

    Telll me how to write to this query using (xmlagg (xmlelement and how to put two columns in (xmlagg (xmlelement .

    pls suggest ...
  • 4. Re: Need your Help for writing complex query !!!
    651776 Newbie
    Currently Being Moderated
    Try this:

    select dist_msisdn, rtrim (xmlagg (xmlelement (e, os_msisdn||':'||rt_count||',' ) order by b).extract ('//text()'), ',') combinatoria_b
    from table
    group by dist_msisdn
  • 5. Re: Need your Help for writing complex query !!!
    surendra4y Newbie
    Currently Being Moderated
    what is rt_count ????????????
    how it will work???????
  • 6. Re: Need your Help for writing complex query !!!
    651776 Newbie
    Currently Being Moderated
    Take a look to this:

    select table_name, rtrim (xmlagg (xmlelement (e, COLUMN_NAME||':'||COLUMN_ID||',' ) order by COLUMN_NAME||':'||COLUMN_ID).extract ('//text()'), ',') combinatoria_b
    from user_tab_columns
    group by table_name

    rt_count is the name of one of the columns that user user3558544 need.

    In the previous query you can ommit the order by statement.
  • 7. Re: Need your Help for writing complex query !!!
    user3558544 Newbie
    Currently Being Moderated
    freind, sorry to say that query provided by you is giving group by error .. kindly suggest query which will give exact result as given below ..

    query result look like ..i.e first column is distinct distributor , second column is fos under distributor , third is RETAILER count under FOS which is already available on rt_count column

    Distributor1, FOS1 :count(RETAILER under FOS1) ,FOS2 :count(RETAILER under FOS2) , FOS3 :count(RETAILER under FOS3)
    like ..

    8751090151(distributor),8751090147(FOS):10(RETAILER COUNT) ,8751090148:25 ,8751090149:11 ....so on....


    TABLE: LBA_ALERT

    date_crt     dist_msisdn      fos_msisdn     rt_count
    26-Mar-13     7399998263     7399998263     1
    26-Mar-13     8751090151     8751090147     10
    26-Mar-13     8751090151     8751090148     25
    26-Mar-13     8751090151     8751090149     11
    26-Mar-13     8751090151     8751090151     1
    26-Mar-13     8751090151     8751090152     40
    26-Mar-13     8751090151     8751090326     11
    26-Mar-13     8751090151     8751090327     70
    26-Mar-13     8751090151     8751090328     29
    26-Mar-13     8751090151     8751090335     1
    26-Mar-13     8751090151     9577162731     8
    26-Mar-13     8751090151     9613381589     38
    26-Mar-13     8751090151     9613381790     14
    26-Mar-13     8751090151     9613381969     13
    26-Mar-13     8751090151     9613385207     42
    26-Mar-13     8751090151     9613385284     12
    26-Mar-13     8751090151     9613386523     52
    26-Mar-13     8751090151     9613389220     91
    26-Mar-13     8751090151     9613530655     1
  • 8. Re: Need your Help for writing complex query !!!
    651776 Newbie
    Currently Being Moderated
    Can you post the query in the exactly way you are runnin it, and the text of the error?

    The query with the name of the table you post might looks similar to this:

    select dist_msisdn, rtrim (xmlagg (xmlelement (e, os_msisdn||':'||rt_count||',' ) order by os_msisdn||':'||rt_count).extract ('//text()'), ',') combinatoria_b
    from LBA_ALERT
    group by dist_msisdn
  • 9. Re: Need your Help for writing complex query !!!
    jeneesh Guru
    Currently Being Moderated
    Did you read any of these links?
    jeneesh wrote:
    Check for PIVOT

    Raed FAQ:

    {message:id=9360005}

    {message:id=9360002}
  • 10. Re: Need your Help for writing complex query !!!
    997945 Newbie
    Currently Being Moderated
    He didnt mention his DB version.
  • 11. Re: Need your Help for writing complex query !!!
    jeneesh Guru
    Currently Being Moderated
    Pradeep wrote:
    He didnt mention his DB version.
    That wont be a problem..

    There are three links in the mentioned post..
  • 12. Re: Need your Help for writing complex query !!!
    user3558544 Newbie
    Currently Being Moderated
    Dear Friend, Thanks a lot for your quick help ...given query is working fine ...

    Edited by: user3558544 on Apr 2, 2013 6:53 AM
  • 13. Re: Need your Help for writing complex query !!!
    651776 Newbie
    Currently Being Moderated
    I'm glad to hear this.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points