13 Replies Latest reply: Apr 2, 2013 10:51 AM by 651776 RSS

    Need your Help for writing complex query !!!

    user3558544
      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
          Check for PIVOT

          Raed FAQ:

          {message:id=9360005}

          {message:id=9360002}
          • 2. Re: Need your Help for writing complex query !!!
            651776
            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
              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
                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
                  what is rt_count ????????????
                  how it will work???????
                  • 6. Re: Need your Help for writing complex query !!!
                    651776
                    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
                      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
                        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
                          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
                            He didnt mention his DB version.
                            • 11. Re: Need your Help for writing complex query !!!
                              jeneesh
                              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
                                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
                                  I'm glad to hear this.