14 Replies Latest reply: Jul 30, 2013 6:54 AM by Rahul_India RSS

    DOUBT

    Rahul_India

      Here is the query

       

      have to fetch the rows (with doctor id, appointment date)

      which does not have G1 rating but has G2 rating for each appointment date

      with data as
          (
             select 111 as doc_id, to_date('23/12/2009', 'dd/mm/yyyy') as app_date, 'G1'   as app_type from dual union all
             select 111 as doc_id, to_date('23/12/2009', 'dd/mm/yyyy') as app_date, 'G2'   as app_type from dual union all
             select 111 as doc_id, to_date('23/12/2009', 'dd/mm/yyyy') as app_date, 'B'    as app_type from dual union all
            select 111 as doc_id, to_date('23/12/2009', 'dd/mm/yyyy') as app_date, 'D'    as app_type from dual union all
           select 111 as doc_id, to_date('23/12/2008', 'dd/mm/yyyy') as app_date, 'G1'   as app_type from dual union all
             select 111 as doc_id, to_date('23/12/2008', 'dd/mm/yyyy') as app_date, 'B'    as app_type from dual union all
            select 111 as doc_id, to_date('23/12/2007', 'dd/mm/yyyy') as app_date, 'G2'   as app_type from dual union all
           select 111 as doc_id, to_date('23/12/2007', 'dd/mm/yyyy') as app_date, 'B'    as app_type from dual union all
           select 111 as doc_id, to_date('23/12/2006', 'dd/mm/yyyy') as app_date, 'D'    as app_type from dual
         )
         
         select doc_id ,app_date from data
         group by doc_id ,app_date
         having 
         count(case when app_TYPE='G1' AND app_TYPE !='G2' THEN 1  ELSE NULL END)=1
      

       

       

      Why this part is not returning the expected data

        count(case when app_TYPE='G1' AND app_TYPE !='G2' THEN 1  ELSE NULL END)=1
      
        • 1. Re: DOUBT
          Hoek

          [removed]

           

          Message was edited by: Hoek  misinterpreted question

          • 2. Re: DOUBT
            Hoek

            What data are you expecting to be returned? I'm unable to parse your phrasing, can you post the expected output?.

            • 3. Re: DOUBT
              Pacmann

              Hi,

               

              You have to count or sum separately, because here it

               

              having count(case when app_TYPE='G1'  THEN 1  ELSE 0 END)=1

              and  count(case when app_TYPE ='G2' THEN 1  ELSE 0 END)= 0

               

              EDIT:

                 TYPE='G1' AND app_TYPE !='G2'  is the same as   TYPE='G1' (because this predicate implies app_TYPE !='G2' )

              • 4. Re: DOUBT
                Sven W.

                The problem is that this expression

                casewhen app_TYPE='G1'AND app_TYPE !='G2'THEN 1  ELSENULLEND"
                
                
                

                works on a single row only.

                 

                But you want a logik that needs to compare the information from different rows.

                The idea to group is a good one, but you have to group first, then compare. You compare first and then group.

                 

                The next lines might work better.

                instead of

                group by doc_id ,app_date 
                having count(case when app_TYPE='G1' AND app_TYPE !='G2' THEN 1  ELSE NULL END)=1
                
                
                

                try

                groupby doc_id ,app_date 
                having count(casewhen app_TYPE='G1' then 1 end) = 0 AND count(app_TYPE ='G2'THEN 1  END) > 0
                
                

                 

                Message was edited by: SvenW. - conndition changed

                • 5. Re: DOUBT
                  Rahul_India

                  My output should be

                   

                  111 23-DEC-07  -- as this has G2 but not G1.

                   

                  I hope you got it.Can you solve using my technique.This question has been asked in the forum before but was solved using analytical functions

                  • 6. Re: DOUBT
                    pollywog
                    WITH data
                         AS (SELECT 111 AS doc_id,
                                    TO_DATE ('23/12/2009', 'dd/mm/yyyy') AS app_date,
                                    'G1' AS app_type
                               FROM DUAL
                             UNION ALL
                             SELECT 111 AS doc_id,
                                    TO_DATE ('23/12/2009', 'dd/mm/yyyy') AS app_date,
                                    'G2' AS app_type
                               FROM DUAL
                             UNION ALL
                             SELECT 111 AS doc_id,
                                    TO_DATE ('23/12/2009', 'dd/mm/yyyy') AS app_date,
                                    'B' AS app_type
                               FROM DUAL
                             UNION ALL
                             SELECT 111 AS doc_id,
                                    TO_DATE ('23/12/2009', 'dd/mm/yyyy') AS app_date,
                                    'D' AS app_type
                               FROM DUAL
                             UNION ALL
                             SELECT 111 AS doc_id,
                                    TO_DATE ('23/12/2008', 'dd/mm/yyyy') AS app_date,
                                    'G1' AS app_type
                               FROM DUAL
                             UNION ALL
                             SELECT 111 AS doc_id,
                                    TO_DATE ('23/12/2008', 'dd/mm/yyyy') AS app_date,
                                    'B' AS app_type
                               FROM DUAL
                             UNION ALL
                             SELECT 111 AS doc_id,
                                    TO_DATE ('23/12/2007', 'dd/mm/yyyy') AS app_date,
                                    'G2' AS app_type
                               FROM DUAL
                             UNION ALL
                             SELECT 111 AS doc_id,
                                    TO_DATE ('23/12/2007', 'dd/mm/yyyy') AS app_date,
                                    'B' AS app_type
                               FROM DUAL
                             UNION ALL
                             SELECT 111 AS doc_id,
                                    TO_DATE ('23/12/2006', 'dd/mm/yyyy') AS app_date,
                                    'D' AS app_type
                               FROM DUAL),
                         t
                         AS (SELECT data.*,
                                    MAX (CASE app_type WHEN 'G1' THEN 1 ELSE 0 END)
                                       OVER (PARTITION BY app_date)
                                       g1_ind,
                                    MAX (CASE app_type WHEN 'G2' THEN 1 ELSE 0 END)
                                       OVER (PARTITION BY app_date)
                                       g2_ind
                               FROM data)
                    SELECT t.doc_id, app_date, app_type
                      FROM t
                     WHERE g1_ind = 0 AND g2_ind = 1
                    
                    • 7. Re: DOUBT
                      BluShadow

                      Maybe not the most performant as it uses an outer join...

                       

                      SQL> ed
                      Wrote file afiedt.buf

                        1  WITH data AS (SELECT 111 AS doc_id, TO_DATE('23/12/2009','dd/mm/yyyy') AS app_date, 'G1' AS app_type FROM DUAL UNION ALL
                        2                SELECT 111 AS doc_id, TO_DATE('23/12/2009','dd/mm/yyyy') AS app_date, 'G2' AS app_type FROM DUAL UNION ALL
                        3                SELECT 111 AS doc_id, TO_DATE('23/12/2009','dd/mm/yyyy') AS app_date, 'B' AS app_type FROM DUAL UNION ALL
                        4                SELECT 111 AS doc_id, TO_DATE('23/12/2009','dd/mm/yyyy') AS app_date, 'D' AS app_type FROM DUAL UNION ALL
                        5                SELECT 111 AS doc_id, TO_DATE('23/12/2008','dd/mm/yyyy') AS app_date, 'G1' AS app_type FROM DUAL UNION ALL
                        6                SELECT 111 AS doc_id, TO_DATE('23/12/2008','dd/mm/yyyy') AS app_date, 'B' AS app_type FROM DUAL UNION ALL
                        7                SELECT 111 AS doc_id, TO_DATE('23/12/2007','dd/mm/yyyy') AS app_date, 'G2' AS app_type FROM DUAL UNION ALL
                        8                SELECT 111 AS doc_id, TO_DATE('23/12/2007','dd/mm/yyyy') AS app_date, 'B' AS app_type FROM DUAL UNION ALL
                        9                SELECT 111 AS doc_id, TO_DATE('23/12/2006','dd/mm/yyyy') AS app_date, 'D' AS app_type FROM DUAL
                      10               )
                      11  --
                      12  -- end of test data
                      13  --
                      14  select d1.doc_id, d1.app_date, d1.app_type
                      15  from data d1 left outer join data d2 on (d2.doc_id = d1.doc_id and d2.app_date = d1.app_date and d2.app_type = 'G1')
                      16  where d1.app_type = 'G2'
                      17* and   d2.doc_id is null
                      SQL> /

                          DOC_ID APP_DATE             AP
                      ---------- -------------------- --
                             111 23-DEC-2007 00:00:00 G2

                       

                      but it's the correct results with simple-to-understand SQL.

                      • 8. Re: DOUBT
                        Hoek

                        Another one:

                         

                        SQL> with data as
                          2     ( select 111 as doc_id, to_date('23/12/2009', 'dd/mm/yyyy') as app_date, 'G1'   as app_type from dual union all
                          3       select 111 as doc_id, to_date('23/12/2009', 'dd/mm/yyyy') as app_date, 'G2'   as app_type from dual union all
                          4       select 111 as doc_id, to_date('23/12/2009', 'dd/mm/yyyy') as app_date, 'B'    as app_type from dual union all
                          5       select 111 as doc_id, to_date('23/12/2009', 'dd/mm/yyyy') as app_date, 'D'    as app_type from dual union all
                          6       select 111 as doc_id, to_date('23/12/2008', 'dd/mm/yyyy') as app_date, 'G1'   as app_type from dual union all
                          7       select 111 as doc_id, to_date('23/12/2008', 'dd/mm/yyyy') as app_date, 'B'    as app_type from dual union all
                          8       select 111 as doc_id, to_date('23/12/2007', 'dd/mm/yyyy') as app_date, 'G2'   as app_type from dual union all
                          9       select 111 as doc_id, to_date('23/12/2007', 'dd/mm/yyyy') as app_date, 'B'    as app_type from dual union all
                        10       select 111 as doc_id, to_date('23/12/2006', 'dd/mm/yyyy') as app_date, 'D'    as app_type from dual
                        11     )
                        12  select doc_id
                        13  ,      app_date
                        14  from   data
                        15  group by doc_id
                        16         , app_date
                        17  having sum(case
                        18                 when app_TYPE = 'G2' THEN 1 
                        19                 when app_TYPE = 'G1' THEN 2  
                        20                 when app_TYPE not in ('G1', 'G2') THEN 0
                        21               end) = 1
                        22  /

                            DOC_ID APP_DATE
                        ---------- -------------------
                               111 23-12-2007 00:00:00

                        1 row selected.

                        • 9. Re: DOUBT
                          BrendanP

                          Just replace your having with this:

                           

                            having count(case when app_TYPE='G1' THEN 1 END) = 0

                            and count(case when app_TYPE ='G2' THEN 1 END) = 1

                          • 10. Re: DOUBT
                            Pacmann

                            Oh just noticed this mixed solution does not work.

                            Either you count and set null unwanted items, or you sum.

                             

                            having count(case when app_TYPE='G1'  THEN 'anythingnotnull'  ELSE null END) >=1

                            and  count(case when app_TYPE ='G2' THEN 'anythingnotnull'  ELSE null END)= 0

                             

                            Is the same as

                             

                            having sum (case when app_TYPE='G1'  THEN 1 ELSE 0 END) >=1

                            and  sum(case when app_TYPE ='G2' THEN 1 ELSE 0 END)= 0

                             

                            Also the "else null" is optional :

                            having count(case when app_TYPE='G1'  THEN 'anythingnotnull'  END) >=1

                            and  count(case when app_TYPE ='G2' THEN 'anythingnotnull'  END)= 0

                            • 11. Re: DOUBT
                              Chris Hunt

                              How about this:

                               

                              with data as

                                   ( select 111 as doc_id, to_date('23/12/2009', 'dd/mm/yyyy') as app_date, 'G1'   as app_type from dual union all

                                     select 111 as doc_id, to_date('23/12/2009', 'dd/mm/yyyy') as app_date, 'G2'   as app_type from dual union all

                                     select 111 as doc_id, to_date('23/12/2009', 'dd/mm/yyyy') as app_date, 'B'    as app_type from dual union all

                                     select 111 as doc_id, to_date('23/12/2009', 'dd/mm/yyyy') as app_date, 'D'    as app_type from dual union all

                                     select 111 as doc_id, to_date('23/12/2008', 'dd/mm/yyyy') as app_date, 'G1'   as app_type from dual union all

                                     select 111 as doc_id, to_date('23/12/2008', 'dd/mm/yyyy') as app_date, 'B'    as app_type from dual union all

                                     select 111 as doc_id, to_date('23/12/2007', 'dd/mm/yyyy') as app_date, 'G2'   as app_type from dual union all

                                     select 111 as doc_id, to_date('23/12/2007', 'dd/mm/yyyy') as app_date, 'B'    as app_type from dual union all

                                     select 111 as doc_id, to_date('23/12/2006', 'dd/mm/yyyy') as app_date, 'D'    as app_type from dual

                                   )

                              select doc_id

                              ,      app_date

                              from   data

                              where  app_type = 'G2'

                              MINUS

                              select doc_id

                              ,      app_date

                              from   data

                              where  app_type = 'G1'

                              • 12. Re: DOUBT
                                Rahul_India

                                How is this being evaluated to get the desired result

                                 

                                sum(case 
                                18                 when app_TYPE = 'G2' THEN 1  
                                19                 when app_TYPE = 'G1' THEN 2   
                                20                 when app_TYPE not in ('G1', 'G2') THEN 0
                                21               end) = 1
                                
                                • 13. Re: DOUBT
                                  Hoek

                                  Just think of CASE as being a 'function'. I'm looking for 'where 1=1', and only when app_type=G2, you get a 1 returned.

                                  If app_type is G1, you get a 2 returned, so 'where 2=1', which will return no rows. For all others app_types a 0 is returned, so they aren't really participating in/influencing the SUM.

                                  Hope it's a bit more clear now.

                                   


                                  • 14. Re: DOUBT
                                    Rahul_India

                                    Ya i got it.I missed the group by clause in your query and got confused how the sum is being use to filter the data