6 Replies Latest reply: Sep 30, 2013 4:13 AM by user12050217 RSS

    Having clause help

    user12050217

      Oracle version 8i (we will migrate this db to 12c in some next months)

       

      Below is table data and defination:

      create table sales

      (

      prod_id varchar2(10),

      prod_name varchar2(30),

      salesman_id varchar2(10),

      salesman_name varchar2(30),

      commission_per varchar2(10),

      commission_amt varchar2(10)

      )

      /

       

      insert into sales values('1','prd1','1','sm1','','10');

      insert into sales values('1','prd1','2','sm2','','10');

      insert into sales values('1','prd1','3','sm3','','10');

       

      insert into sales values('2','prd2','1','sm1','10.00','');

      insert into sales values('2','prd2','2','sm2','10.00','');

       

      insert into sales values('3','prd3','2','sm2','10.00','');

      insert into sales values('3','prd3','1','sm1','15.00','');

      insert into sales values('3','prd3','3','sm3','10.00','');

       

      insert into sales values('4','prd4','1','sm1','10.00','');

      insert into sales values('4','prd4','2','sm2','','15');

       

      insert into sales values('5','prd5','1','sm1','','15');

       

      insert into sales values('6','prd6','1','sm1','','15');

      insert into sales values('6','prd6','2','sm2','','15');

      insert into sales values('6','prd6','3','sm3','15','');

      insert into sales values('6','prd6','4','sm4','15','');

       

      insert into sales values('7','prd7','1','sm1','','15');

      insert into sales values('7','prd7','2','sm2','','15');

      insert into sales values('7','prd7','3','sm3','','15');

      insert into sales values('7','prd7','4','sm4','','15');

      insert into sales values('7','prd7','7','sm7','','15');

      insert into sales values('7','prd7','5','sm1','','10');

      insert into sales values('7','prd7','6','sm6','','15');

       

      Required output please:

      Unique Commision Amount/Commission Percentage:
      PROD_ID    PROD_NAME                      SALESMAN_I SALESMAN_NAME                  COMMISSION COMMISSION
      ---------- ------------------------------ ---------- ------------------------------ ---------- ----------
      1          prd1                           1          sm1                                       10
      2          prd2                           1          sm1                            10.00
      5          prd5                           1          sm1                                       15
      

      Means, we have to identify those product ids for which company gives same sales commission amount or commission percentage. Different Commision Amount/Commission Percentage:

      Different Commision Amount/Commission Percentage:
      PROD_ID    PROD_NAME                      SALESMAN_I SALESMAN_NAME                  COMMISSION COMMISSION
      ---------- ------------------------------ ---------- ------------------------------ ---------- ----------
      3          prd3                           2          sm2                            10.00
      3          prd3                           1          sm1                            15.00
      3          prd3                           3          sm3                            10.00
      4          prd4                           1          sm1                            10.00
      4          prd4                           2          sm2                                       15
      6          prd6                           1          sm1                                       15
      6          prd6                           2          sm2                                       15
      6          prd6                           3          sm3                            15
      6          prd6                           4          sm4                            15
      7          prd7                           1          sm1                                       15
      7          prd7                           2          sm2                                       15
      7          prd7                           3          sm3                                       15
      7          prd7                           4          sm4                                       15
      7          prd7                           7          sm7                                       15
      7          prd7                           5          sm1                                       10
      7          prd7                           6          sm6                                       15
      

      Means, for above product ids, company gives different sales commission amount/percentage to salesmen; i.e. if for a product id, there is same commission amount/percentage that should be come in first output, else those products ids rows should be in second output. Kindly help me, since we are using very older version, so I think we can not use analytics or newer SQL functions.  So, please help me in a simple sql. Thanks.

        • 1. Re: Having clause help
          Karthick_Arp

          I hope this works in 8i version.

           

          SQL> select prod_id
            2       , prod_name
            3       , salesman_id
            4       , salesman_name
            5       , commission_per
            6       , commission_amt
            7    from (
            8            select s.*
            9                 , mod(nvl(sum(commission_per) over(partition by prod_id), 0), count(*) over(partition  by prod_id)) +
          10                   mod(nvl(sum(commission_amt) over(partition by prod_id), 0), count(*) over(partition  by prod_id)) val
          11                 , row_number() over(partition by prod_id order by salesman_id) rno
          12              from sales s
          13         )
          14   where val = 0
          15     and rno = 1;

           

          PROD_ID    PROD_NAME                      SALESMAN_I SALESMAN_NAME                  COMMISSION COMMISSION
          ---------- ------------------------------ ---------- ------------------------------ ---------- ----------
          1          prd1                           1          sm1                                       10
          2          prd2                           1          sm1                            10.00
          5          prd5                           1          sm1                                       15

           

          SQL> select prod_id
            2       , prod_name
            3       , salesman_id
            4       , salesman_name
            5       , commission_per
            6       , commission_amt
            7    from (
            8            select s.*
            9                 , mod(nvl(sum(commission_per) over(partition by prod_id), 0), count(*) over(partition  by prod_id)) +
          10                   mod(nvl(sum(commission_amt) over(partition by prod_id), 0), count(*) over(partition  by prod_id)) val
          11                 , row_number() over(partition by prod_id order by salesman_id) rno
          12              from sales s
          13         )
          14   where val != 0;

           

          PROD_ID    PROD_NAME                      SALESMAN_I SALESMAN_NAME                  COMMISSION COMMISSION
          ---------- ------------------------------ ---------- ------------------------------ ---------- ----------
          3          prd3                           2          sm2                            10.00
          3          prd3                           1          sm1                            15.00
          3          prd3                           3          sm3                            10.00
          4          prd4                           1          sm1                            10.00
          4          prd4                           2          sm2                                       15
          6          prd6                           2          sm2                                       15
          6          prd6                           1          sm1                                       15
          6          prd6                           4          sm4                            15
          6          prd6                           3          sm3                            15
          7          prd7                           7          sm7                                       15
          7          prd7                           5          sm1                                       10
          7          prd7                           4          sm4                                       15
          7          prd7                           3          sm3                                       15
          7          prd7                           6          sm6                                       15
          7          prd7                           1          sm1                                       15
          7          prd7                           2          sm2                                       15

           

          16 rows selected.

           

          SQL>

          • 2. Re: Having clause help
            user12050217

            Thank you so much sir.

            • 3. Re: Having clause help
              Karthick_Arp

              user12050217 wrote:

               

              Thank you so much sir.

               

              Sorry that was a incorrect answer. Try this.

               

              select prod_id                                                                      

                   , prod_name                                                                    

                   , salesman_id                                                                  

                   , salesman_name                                                                

                   , commission_per                                                               

                   , commission_amt

                from (    

                        select prod_id                                                            

                             , prod_name                                                          

                             , salesman_id                                                        

                             , salesman_name                                                      

                             , commission_per                                                     

                             , commission_amt 

                             , count(distinct nvl(commission_per, 0)) over(partition by prod_id) c1

                             , count(distinct nvl(commission_amt, 0)) over(partition by prod_id) c2

                             , row_number() over(partition by prod_id order by salesman_id) rno

                         from sales

                     )

              where c1 = 1 and c2 = 1 and rno = 1;    

               

              select prod_id                                                                     

                   , prod_name                                                                    

                   , salesman_id                                                                  

                   , salesman_name                                                                

                   , commission_per                                                               

                   , commission_amt

                from (    

                        select prod_id                                                            

                             , prod_name                                                          

                             , salesman_id                                                        

                             , salesman_name                                                      

                             , commission_per                                                     

                             , commission_amt 

                             , count(distinct nvl(commission_per, 0)) over(partition by prod_id) c1

                             , count(distinct nvl(commission_amt, 0)) over(partition by prod_id) c2

                             , row_number() over(partition by prod_id order by salesman_id) rno

                         from sales

                     )

              where c1 != 1 or c2 != 1;   

              • 4. Re: Having clause help
                user12050217

                >Sorry that was a incorrect answer. Try this.

                How and why above query was incorrect please?

                • 5. Re: Having clause help
                  Karthick_Arp

                  user12050217 wrote:

                   

                  >Sorry that was a incorrect answer. Try this.

                  How and why above query was incorrect please?

                   

                  I am just summing up the value and dividing by count and checking if the output is a whole number. That can be true even if the values are not unique. Use the second solution provided by me.

                  • 6. Re: Having clause help
                    user12050217

                    Ok.  Thank you sir.