1 2 Previous Next 16 Replies Latest reply: Dec 3, 2012 6:35 AM by 711584 RSS

    Problem  While Writing SubQuery

    711584
      Hi:

      There are three tables,

      1.TbReq
      req_no,req_name
      Data:
      1 Req1
      2 Req2
      3 Req3

      2.TbSts
      sts_no,st_name
      Data:
      1 Approved
      2 Pending
      3 Downloaded


      3.TbReqTrn
      trn_no,req_no,sts_no
      Data:

      1 1 1
      2 1 2
      3 2 2

      Required O/p:*

      Reuest Type | NoOfRequest| NoOfStatusPending

      Req1| 2 | 1
      Req2| 1 | 1
      Req3| 0 | 0




      From Above tables i want to write query for,

      select total number of requests available in TbReqTrn against each request from TbReq
      and total number of status where st_name like '*Pending*'(and we have to take this count also from TbReqTrn table)

      So, how to write this query...?

      Can any body help me...


      Thank You,
      Anup Desai

      Edited by: user11688398 on Dec 3, 2012 2:33 AM

      Edited by: user11688398 on Dec 3, 2012 2:39 AM

      Edited by: user11688398 on Dec 3, 2012 2:40 AM
        • 1. Re: Problem  While Writing SubQuery
          jeneesh
          Read FAQ: {message:id=9360002}

          Provide your db version, sample data (CREATE TABLE and INSERT statements) and expected output..
          • 2. Re: Problem  While Writing SubQuery
            ranit B
            1.TbReq
            req_no,req_name

            2.TbSts
            sts_no,st_name

            3.TbReqTrn
            trn_no,req_no,sts_no

            From Above tables i want to write query for,

            select total number of requests available in TbReqTrn against each request from TbReq
            and total number of status where st_name like '*Pending*'(and we have to take this count also from TbReqTrn table)
            Try this... NOT TESTED...
            /* Formatted on 12-3-2012 3:57:31 PM (QP5 v5.163.1008.3004) */
            SELECT COUNT (a.trn_no)
              FROM tbreqtrn a, tbreq b, tbsts c
             WHERE a.regno = b.regno AND a.sts_no = c.sts_no AND c.st_name = 'Pending';
            • 3. Re: Problem  While Writing SubQuery
              Most Wanted!!!!
              try this
              SELECT COUNT (a.req_no)
                FROM tbreq a
               WHERE EXISTS (
                        SELECT 1
                          FROM tbreqtrn b
                         WHERE a.req_no = b.req_no
                           AND EXISTS (
                                    SELECT 1
                                      FROM tbsts c
                                     WHERE b.sts_no = c.sts_no
                                           AND c.st_name LIKE '%PENDING%'));
              SELECT COUNT (a.req_no)
                FROM tbreq a
               WHERE EXISTS (
                        SELECT 1
                          FROM tbreqtrn b
                         WHERE a.req_no = b.req_no
                           AND EXISTS (
                                    SELECT 1
                                      FROM tbsts c
                                     WHERE b.sts_no = c.sts_no
                                           AND c.st_name ='PENDING'));
              regards,
              friend

              Edited by: most wanted!!!! on Dec 3, 2012 2:44 AM

              Edited by: most wanted!!!! on Dec 3, 2012 2:46 AM
              • 4. Re: Problem  While Writing SubQuery
                711584
                Hi:

                Please check some sample I/O:


                There are three tables,

                1.TbReq
                req_no,req_name
                Data:
                1 Req1
                2 Req2
                3 Req3

                2.TbSts
                sts_no,st_name
                Data:
                1 Approved
                2 Pending
                3 Downloaded


                3.TbReqTrn
                trn_no,req_no,sts_no
                Data:

                1 1 1
                2 1 2
                3 2 2

                Required O/p:

                Reuest Type | NoOfRequest| NoOfStatusPending

                Req1| 2 | 1
                Req2| 1 | 1
                Req3| 0 | 0




                Thank You,
                Anup Desai
                • 5. Re: Problem  While Writing SubQuery
                  711584
                  Hi:

                  Please check my Input and required output


                  Thank You,
                  Anup Desai
                  • 6. Re: Problem  While Writing SubQuery
                    Most Wanted!!!!
                    WITH tbreq AS
                         (SELECT 1 AS req_no, 'req1' AS req_name
                            FROM DUAL
                          UNION ALL
                          SELECT 2 AS req_no, 'req2' AS req_name
                            FROM DUAL
                          UNION ALL
                          SELECT 3 AS req_no, 'req3' AS req_name
                            FROM DUAL),
                         tbsts AS
                         (SELECT 1 AS sts_no, 'APPROVED' AS st_name
                            FROM DUAL
                          UNION ALL
                          SELECT 2 AS sts_no, 'PENDING' AS st_name
                            FROM DUAL
                          UNION ALL
                          SELECT 3 AS sts_no, 'DOWNLOADED' AS st_name
                            FROM DUAL),
                         tbreqtrn AS
                         (SELECT 1 AS trn_no, 1 AS req_no, 1 AS sts_no
                            FROM DUAL
                          UNION ALL
                          SELECT 2 AS trn_no, 1 AS req_no, 2 AS sts_no
                            FROM DUAL
                          UNION ALL
                          SELECT 3 AS trn_no, 2 AS req_no, 2 AS sts_no
                            FROM DUAL)
                    SELECT   a.req_name AS req_type, COUNT (a.req_no), COUNT (c.sts_no)
                        FROM tbreq a, tbreqtrn b, tbsts c
                       WHERE a.req_no = b.req_no
                         AND b.sts_no = c.sts_no
                         AND c.st_name LIKE '%PENDING%'
                    GROUP BY a.req_name, a.req_no, c.sts_no;
                    • 7. Re: Problem  While Writing SubQuery
                      711584
                      No, It gives me wrong output.

                      Thank You,
                      Anup Desai
                      • 8. Re: Problem  While Writing SubQuery
                        jeneesh
                        select req.req_name,
                               sum(decode(trn.req_no,null,0,1)) req_count,
                               sum(decode(sts.st_name,'Pending',1,0)) pend_count
                        from tbreqtrn trn right outer join tbreq req
                          on (trn.req_no = req.req_no)
                          left outer join tbsts sts
                          on (sts.sts_no = trn.sts_no)
                        group by req.req_name  
                        order by 1;
                        Or the "+" notation
                        select req.req_name,
                               sum(decode(trn.req_no,null,0,1)) req_count,
                               sum(decode(sts.st_name,'Pending',1,0)) pend_count
                        from tbreqtrn trn,tbreq req,tbsts sts
                        where trn.req_no(+) = req.req_no
                        and sts.sts_no(+) = trn.sts_no
                        group by req.req_name  
                        order by 1;
                        Edited by: jeneesh on Dec 3, 2012 4:54 PM
                        • 9. Re: Problem  While Writing SubQuery
                          711584
                          In this query sum(decode(sts.st_name,'Pending',1,0)) pend_count gets fail. Because in my case i want check names like, Approval Pending , Pending ,Download Pending...etc. So i want to use like'%Pending%'

                          So how to solve it?


                          Thank You,
                          Anup Desai
                          • 10. Re: Problem  While Writing SubQuery
                            jeneesh
                            Use CASE
                            sum(case when lower(sts.st_name) like '%pending%' then 1 else 0 end) pend_count
                            • 11. Re: Problem  While Writing SubQuery
                              Most Wanted!!!!
                              WITH tbreq AS
                                   (SELECT 1 AS req_no, 'REQ1' AS req_name
                                      FROM DUAL
                                    UNION ALL
                                    SELECT 2 AS req_no, 'REQ2' AS req_name
                                      FROM DUAL
                                    UNION ALL
                                    SELECT 3 AS req_no, 'REQ3' AS req_name
                                      FROM DUAL),
                                   tbsts AS
                                   (SELECT 1 AS sts_no, 'APPROVED' AS st_name
                                      FROM DUAL
                                    UNION ALL
                                    SELECT 2 AS sts_no, 'PENDING' AS st_name
                                      FROM DUAL
                                    UNION ALL
                                    SELECT 3 AS sts_no, 'DOWNLOADED' AS st_name
                                      FROM DUAL),
                                   tbreqtrn AS
                                   (SELECT 1 AS trn_no, 1 AS req_no, 1 AS sts_no
                                      FROM DUAL
                                    UNION ALL
                                    SELECT 2 AS trn_no, 1 AS req_no, 2 AS sts_no
                                      FROM DUAL
                                    UNION ALL
                                    SELECT 3 AS trn_no, 2 AS req_no, 2 AS sts_no
                                      FROM DUAL)
                              SELECT DISTINCT req_name,
                                              SUM (CASE
                                                      WHEN a.req_no <> c.req_no
                                                         THEN 0
                                                      ELSE 1
                                                   END) AS no_of_req,
                                              CASE
                                                 WHEN b.st_name LIKE '%PEN%'
                                                    THEN 1
                                                 ELSE 0
                                              END no_of_pend_status
                                         FROM tbreq a, tbsts b, tbreqtrn c
                                        WHERE st_name LIKE '%PEN%'
                                     GROUP BY a.req_name, b.st_name
                                     ORDER BY req_name
                              • 12. Re: Problem  While Writing SubQuery
                                jeneesh
                                most wanted!!!! wrote:
                                WITH tbreq AS
                                (SELECT 1 AS req_no, 'REQ1' AS req_name
                                FROM DUAL
                                UNION ALL
                                SELECT 2 AS req_no, 'REQ2' AS req_name
                                FROM DUAL
                                UNION ALL
                                SELECT 3 AS req_no, 'REQ3' AS req_name
                                FROM DUAL),
                                tbsts AS
                                (SELECT 1 AS sts_no, 'APPROVED' AS st_name
                                FROM DUAL
                                UNION ALL
                                SELECT 2 AS sts_no, 'PENDING' AS st_name
                                FROM DUAL
                                UNION ALL
                                SELECT 3 AS sts_no, 'DOWNLOADED' AS st_name
                                FROM DUAL),
                                tbreqtrn AS
                                (SELECT 1 AS trn_no, 1 AS req_no, 1 AS sts_no
                                FROM DUAL
                                UNION ALL
                                SELECT 2 AS trn_no, 1 AS req_no, 2 AS sts_no
                                FROM DUAL
                                UNION ALL
                                SELECT 3 AS trn_no, 2 AS req_no, 2 AS sts_no
                                FROM DUAL)
                                SELECT DISTINCT req_name,
                                SUM (CASE
                                WHEN a.req_no <> c.req_no
                                THEN 0
                                ELSE 1
                                END) AS no_of_req,
                                CASE
                                WHEN b.st_name LIKE '%PEN%'
                                THEN 1
                                ELSE 0
                                END no_of_pend_status
                                FROM tbreq a, tbsts b, tbreqtrn c
                                WHERE st_name LIKE '%PEN%'
                                GROUP BY a.req_name, b.st_name
                                ORDER BY req_name
                                Lot of issues in the code..

                                1. No need to use DISTINCT, when you are GROUPING
                                2. No join conditions applied...outer join is required..
                                3. Unwanted filter.
                                4. Unwantd column in the GROUP BY list
                                • 13. Re: Problem  While Writing SubQuery
                                  Most Wanted!!!!
                                  thank you for identifying the issues jeneesh am a newbie i have just started learning.

                                  Edited by: most wanted!!!! on Dec 3, 2012 3:59 AM
                                  • 14. Re: Problem  While Writing SubQuery
                                    711584
                                    Hi this query works fine. But last thing is that i want to display only such records whose req_count>0


                                    Thank You,
                                    Anup Desai
                                    1 2 Previous Next