1 2 3 Previous Next 36 Replies Latest reply: Mar 11, 2013 10:55 AM by 994691 Go to original post RSS
      • 15. Re: ORA-01427:single-row sub query returns more than one row (group by)
        989009
        well.. for test purpose... you can always replace sysdate to be another date which could return data...
        if they are returning the same... which they should... then you can run the select I post earlier to see if the value in that column is what you are looking for...
        it is .. all you need to do is to rewrite other three selects to follow the same syntax as the working one...
        • 16. Re: ORA-01427:single-row sub query returns more than one row (group by)
          989009
          now if you run
          SELECT SUM(Wip_To_Bill_Loc_Amt) AS Wip_To_Bill_Loc_Amt,
                 SUM(Realization_Loc_Amt) AS Realization_Loc_Amt,
                 SUM(Neg_Real_Loc_Amt) AS Neg_Real_Loc_Amt,
                 SUM(Pos_Real_Loc_Amt) AS Pos_Real_Loc_Amt,
                 SUM(Bill_In_Advance_Loc_Amt) AS Bill_In_Advance_Loc_Amt,
                 SUM(Carry_Forward_Loc_Amt) AS Carry_Forward_Loc_Amt,
                 SUM(Bill_To_Client_Loc_Amt) AS Bill_To_Client_Loc_Amt,
                 SUM(Remain_Wip_To_Bill_Loc_Amt) AS Remain_Wip_To_Bill_Loc_Amt,
                 SUM(Ar_Inv_Amt) AS Ar_Inv_Amt,
                 SUM(Ar_Tax_Amt) AS Ar_Tax_Amt,
                 COUNT(DISTINCT CASE
                         WHEN ((Inv_Status_Type = 'FIN' AND
                              Inv_Status_Code NOT IN ('COMPLETE', 'PROCESSED')) OR
                              (Inv_Status_Type = 'WS' AND Inv_Status_Code NOT IN ('PRC'))) --COMPLETED
                              AND To_Char((SYSDATE - 5), 'YYYYMMDD') BETWEEN
                              To_Char(Status_Start_Dt, 'YYYYMMDD') AND
                              To_Char(Status_End_Dt, 'YYYYMMDD') THEN
                          Rpad(Integration_Id, 32)
                       END) AS Beg_Bal_Wkst_Num,
                 Duration AS Duration,
                 Num_Days AS Num_Days,
                 Num_Hours AS Num_Hours,
                 Num_Minutes AS Num_Minutes,
                 Num_Seconds AS Num_Seconds,
                 Lead_Project_Office_Code AS Lead_Project_Office_Code,
                 Lead_Project_Team_Code AS Lead_Project_Team_Code,
                 Org_Id AS Org_Id,
                 Trunc(SYSDATE - 1) AS Rpt_Date,
                 To_Char((SYSDATE - 1), 'YYYYMMDD') AS Rpt_Date_Wid,
                 --last_day(a.report_date) mth_end_dt,
                 Loc_Curr_Code AS Local_Curr_Code,
                 Usd_Exch_Rate AS Usd_Exch_Rate,
                 Eur_Exch_Rate AS Eur_Exch_Rate,
                 Gbp_Exch_Rate AS Gbp_Exch_Rate
            FROM Wc_Twfs_Olb_Invoice_History_f
           WHERE Rpt_Dt_Mcal_Period_Wid =
                 (SELECT MAX(Rpt_Dt_Mcal_Period_Wid)
                    FROM Wc_Twfs_Olb_Invoice_History_f)
           GROUP BY 
                    Duration,
                    Num_Days,
                    Num_Hours,
                    Num_Minutes,
                    Num_Seconds,
                    Lead_Project_Office_Code,
                    Lead_Project_Team_Code,
                    Org_Id,
                    Rpt_Date,
                    Rpt_Date_Wid,
                    Local_Curr_Code,
                    Usd_Exch_Rate,
                    Eur_Exch_Rate,
                    Gbp_Exch_Rate;
          it should give you a break down of Beg_Bal_Wkst_Num based on the grouping
          • 17. Re: ORA-01427:single-row sub query returns more than one row (group by)
            994691
            But still it is returning the same number to all the rows

            for eg:- count of(distinct(olb_inv_row_wid)) for Beg_Bal_Wkst_Num is 26382
            count of (distinct(olb_inv_row_wid)) for Wkst_Received_num is 16932
            count of(distinct(olb_inv_row_wid)) for Wkst_processed_num is 4322
            count of (distinct(olb_inv_row_wid)) for Wkst_canceled_num is 0

            Then it is giving
            Beg_Bal_wkst_num wkst_received_num wkst_processed_num wkst_canceled_num
            26382 16932 4322 0
            26382 16932 4322 0
            26382 16932 4322 0 ........... so on. I want this total value to be splited, means they are not in group, I want to make them a group by with, lead_project_office_code, lead_project_team_code,org_id,loc_curr_code,usd_exch_rate,eur_exch_rat,gbp_exch_rate..
            • 18. Re: ORA-01427:single-row sub query returns more than one row (group by)
              989009
              that was not the the select I put there....
              but I guess.. I will just rewrite that for you
              SELECT SUM(Wip_To_Bill_Loc_Amt) AS Wip_To_Bill_Loc_Amt,
                     SUM(Realization_Loc_Amt) AS Realization_Loc_Amt,
                     SUM(Neg_Real_Loc_Amt) AS Neg_Real_Loc_Amt,
                     SUM(Pos_Real_Loc_Amt) AS Pos_Real_Loc_Amt,
                     SUM(Bill_In_Advance_Loc_Amt) AS Bill_In_Advance_Loc_Amt,
                     SUM(Carry_Forward_Loc_Amt) AS Carry_Forward_Loc_Amt,
                     SUM(Bill_To_Client_Loc_Amt) AS Bill_To_Client_Loc_Amt,
                     SUM(Remain_Wip_To_Bill_Loc_Amt) AS Remain_Wip_To_Bill_Loc_Amt,
                     SUM(Ar_Inv_Amt) AS Ar_Inv_Amt,
                     SUM(Ar_Tax_Amt) AS Ar_Tax_Amt,
                     COUNT(DISTINCT CASE
                             WHEN ((Inv_Status_Type = 'FIN' AND
                                  Inv_Status_Code NOT IN ('COMPLETE', 'PROCESSED')) OR
                                  (Inv_Status_Type = 'WS' AND Inv_Status_Code NOT IN ('PRC'))) --COMPLETED
                                  AND To_Char((SYSDATE - 5), 'YYYYMMDD') BETWEEN
                                  To_Char(Status_Start_Dt, 'YYYYMMDD') AND
                                  To_Char(Status_End_Dt, 'YYYYMMDD') THEN
                              Rpad(Integration_Id, 32)
                           END) AS Beg_Bal_Wkst_Num,
                     COUNT(DISTINCT CASE
                             WHEN (Inv_Status_Code = 'NEW') AND
                                  To_Char((SYSDATE - 4), 'YYYYMMDD') BETWEEN
                                  To_Char(Status_Start_Dt, 'YYYYMMDD') AND
                                  To_Char(Status_End_Dt, 'YYYYMMDD') THEN
                              Rpad(Integration_Id, 32)
                           END) AS Wkst_Received_Num,
                     COUNT(DISTINCT CASE
                             WHEN ((Inv_Status_Type = 'FIN' AND
                                  Inv_Status_Code IN ('COMPLETE', 'PROCESSED')) OR
                                  (Inv_Status_Type = 'WS' AND Inv_Status_Code IN ('PRC'))) --COMPLETED
                                  AND To_Char((SYSDATE - 4), 'YYYYMMDD') BETWEEN
                                  To_Char((Status_Start_Dt), 'YYYYMMDD') AND
                                  To_Char((Status_End_Dt), 'YYYYMMDD') THEN
                              Rpad(Integration_Id, 32)
                           END) AS Wkst_Processed_Num,
                     COUNT(DISTINCT CASE
                             WHEN (Inv_Status_Type = 'FIN' AND Inv_Status_Code = 'CANCELLED') AND
                                  To_Char((SYSDATE - 4), 'YYYYMMDD') BETWEEN
                                  To_Char((Status_Start_Dt), 'YYYYMMDD') AND
                                  To_Char((Status_End_Dt), 'YYYYMMDD') THEN
                              Rpad(Integration_Id, 32)
                           END) AS Wkst_Canceled_Num,
                     Duration AS Duration,
                     Num_Days AS Num_Days,
                     Num_Hours AS Num_Hours,
                     Num_Minutes AS Num_Minutes,
                     Num_Seconds AS Num_Seconds,
                     Lead_Project_Office_Code AS Lead_Project_Office_Code,
                     Lead_Project_Team_Code AS Lead_Project_Team_Code,
                     Org_Id AS Org_Id,
                     Trunc(SYSDATE - 1) AS Rpt_Date,
                     To_Char((SYSDATE - 1), 'YYYYMMDD') AS Rpt_Date_Wid,
                     --last_day(a.report_date) mth_end_dt,
                     Loc_Curr_Code AS Local_Curr_Code,
                     Usd_Exch_Rate AS Usd_Exch_Rate,
                     Eur_Exch_Rate AS Eur_Exch_Rate,
                     Gbp_Exch_Rate AS Gbp_Exch_Rate
                FROM Wc_Twfs_Olb_Invoice_History_f
               WHERE Rpt_Dt_Mcal_Period_Wid =
                     (SELECT MAX(Rpt_Dt_Mcal_Period_Wid)
                        FROM Wc_Twfs_Olb_Invoice_History_f)
               GROUP BY Duration,
                        Num_Days,
                        Num_Hours,
                        Num_Minutes,
                        Num_Seconds,
                        Lead_Project_Office_Code,
                        Lead_Project_Team_Code,
                        Org_Id,
                        Rpt_Date,
                        Rpt_Date_Wid,
                        Local_Curr_Code,
                        Usd_Exch_Rate,
                        Eur_Exch_Rate,
                        Gbp_Exch_Rate;
              Try this
              • 19. Re: ORA-01427:single-row sub query returns more than one row (group by)
                994691
                Thanks I think it is returning the values, but It is started with 0, when we scroll down it is 1, till down 2....... Can we do any order by so that bigger number comes up and lower number's goes down. the complete query is returning 33873 rows.

                But this is what it mean, those columns are now grouped based on the other columns that we specified in the group by right.
                • 20. Re: ORA-01427:single-row sub query returns more than one row (group by)
                  989009
                  You can just simply add order by on the columns you wand to sort on
                  SELECT SUM(Wip_To_Bill_Loc_Amt) AS Wip_To_Bill_Loc_Amt,
                         SUM(Realization_Loc_Amt) AS Realization_Loc_Amt,
                         SUM(Neg_Real_Loc_Amt) AS Neg_Real_Loc_Amt,
                         SUM(Pos_Real_Loc_Amt) AS Pos_Real_Loc_Amt,
                         SUM(Bill_In_Advance_Loc_Amt) AS Bill_In_Advance_Loc_Amt,
                         SUM(Carry_Forward_Loc_Amt) AS Carry_Forward_Loc_Amt,
                         SUM(Bill_To_Client_Loc_Amt) AS Bill_To_Client_Loc_Amt,
                         SUM(Remain_Wip_To_Bill_Loc_Amt) AS Remain_Wip_To_Bill_Loc_Amt,
                         SUM(Ar_Inv_Amt) AS Ar_Inv_Amt,
                         SUM(Ar_Tax_Amt) AS Ar_Tax_Amt,
                         COUNT(DISTINCT CASE
                                 WHEN ((Inv_Status_Type = 'FIN' AND
                                      Inv_Status_Code NOT IN ('COMPLETE', 'PROCESSED')) OR
                                      (Inv_Status_Type = 'WS' AND Inv_Status_Code NOT IN ('PRC'))) --COMPLETED
                                      AND To_Char((SYSDATE - 5), 'YYYYMMDD') BETWEEN
                                      To_Char(Status_Start_Dt, 'YYYYMMDD') AND
                                      To_Char(Status_End_Dt, 'YYYYMMDD') THEN
                                  Rpad(Integration_Id, 32)
                               END) AS Beg_Bal_Wkst_Num,
                         COUNT(DISTINCT CASE
                                 WHEN (Inv_Status_Code = 'NEW') AND
                                      To_Char((SYSDATE - 4), 'YYYYMMDD') BETWEEN
                                      To_Char(Status_Start_Dt, 'YYYYMMDD') AND
                                      To_Char(Status_End_Dt, 'YYYYMMDD') THEN
                                  Rpad(Integration_Id, 32)
                               END) AS Wkst_Received_Num,
                         COUNT(DISTINCT CASE
                                 WHEN ((Inv_Status_Type = 'FIN' AND
                                      Inv_Status_Code IN ('COMPLETE', 'PROCESSED')) OR
                                      (Inv_Status_Type = 'WS' AND Inv_Status_Code IN ('PRC'))) --COMPLETED
                                      AND To_Char((SYSDATE - 4), 'YYYYMMDD') BETWEEN
                                      To_Char((Status_Start_Dt), 'YYYYMMDD') AND
                                      To_Char((Status_End_Dt), 'YYYYMMDD') THEN
                                  Rpad(Integration_Id, 32)
                               END) AS Wkst_Processed_Num,
                         COUNT(DISTINCT CASE
                                 WHEN (Inv_Status_Type = 'FIN' AND Inv_Status_Code = 'CANCELLED') AND
                                      To_Char((SYSDATE - 4), 'YYYYMMDD') BETWEEN
                                      To_Char((Status_Start_Dt), 'YYYYMMDD') AND
                                      To_Char((Status_End_Dt), 'YYYYMMDD') THEN
                                  Rpad(Integration_Id, 32)
                               END) AS Wkst_Canceled_Num,
                         Duration AS Duration,
                         Num_Days AS Num_Days,
                         Num_Hours AS Num_Hours,
                         Num_Minutes AS Num_Minutes,
                         Num_Seconds AS Num_Seconds,
                         Lead_Project_Office_Code AS Lead_Project_Office_Code,
                         Lead_Project_Team_Code AS Lead_Project_Team_Code,
                         Org_Id AS Org_Id,
                         Trunc(SYSDATE - 1) AS Rpt_Date,
                         To_Char((SYSDATE - 1), 'YYYYMMDD') AS Rpt_Date_Wid,
                         --last_day(a.report_date) mth_end_dt,
                         Loc_Curr_Code AS Local_Curr_Code,
                         Usd_Exch_Rate AS Usd_Exch_Rate,
                         Eur_Exch_Rate AS Eur_Exch_Rate,
                         Gbp_Exch_Rate AS Gbp_Exch_Rate
                    FROM Wc_Twfs_Olb_Invoice_History_f
                   WHERE Rpt_Dt_Mcal_Period_Wid =
                         (SELECT MAX(Rpt_Dt_Mcal_Period_Wid)
                            FROM Wc_Twfs_Olb_Invoice_History_f)
                   GROUP BY Duration,
                            Num_Days,
                            Num_Hours,
                            Num_Minutes,
                            Num_Seconds,
                            Lead_Project_Office_Code,
                            Lead_Project_Team_Code,
                            Org_Id,
                            Rpt_Date,
                            Rpt_Date_Wid,
                            Local_Curr_Code,
                            Usd_Exch_Rate,
                            Eur_Exch_Rate,
                            Gbp_Exch_Rate
                  order by Beg_Bal_Wkst_Num, Wkst_Received_Num, Wkst_Processed_Num, Wkst_Canceled_Num DESC;
                  • 21. Re: ORA-01427:single-row sub query returns more than one row (group by)
                    994691
                    Thank you very much, This helped me a lot. But I have not tested the data yet. I will test the data. but the values are splited across and I think now those columns are grouped.

                    Thank you Y.L
                    • 22. Re: ORA-01427:single-row sub query returns more than one row (group by)
                      989009
                      You are welcome... and you were right... those values are based on the grouping in the group by clause
                      • 23. Re: ORA-01427:single-row sub query returns more than one row (group by)
                        994691
                        Hi , I have tried it to load the data in to the table, but I am getting the duplicate rows. But in the query we have tried for distinct in sub query. Can you please tell me what is causing it to have the duplicated rows.
                        • 24. Re: ORA-01427:single-row sub query returns more than one row (group by)
                          989009
                          not sure what do you mean by duplicate... post some duplicate rows here would help so I can understand what gets duplicated
                          • 25. Re: ORA-01427:single-row sub query returns more than one row (group by)
                            994691
                            In the target table I have Lead_project_office_code,lead_project_team_code,org_id as not null.
                            rpt_date,local_curr_code,usd_exch_rate,eur_exch_rate,gbp_exch_rate,duration,num_days,num_hours,num_minutes,num_seconds as nullable.

                            I am having indexes defined on Lead_project_office_code,lead_project_team_code,org_id,rpt_date.

                            I am getting a duplicate rows, this is not allowing me to write all the date to the target table.

                            The source is reading 28632 rows but it is writing only 1235 rows to my target table. And I could see error as unique key constraint violated. Can you please suggest me any suggestions or, what would be the next move on this. Thanks.
                            • 26. Re: ORA-01427:single-row sub query returns more than one row (group by)
                              989009
                              I am having indexes defined on Lead_project_office_code,lead_project_team_code,org_id,rpt_date.
                              
                              I am getting a duplicate rows, this is not allowing me to write all the date to the target table.
                              
                              The source is reading 28632 rows but it is writing only 1235 rows to my target table. And I could see error as unique key constraint violated. Can you please suggest me any suggestions or, what would be the next move on this. Thanks.
                              you are getting unique key constraint violation... first you should get out of this error message is that the column you have in this unique index is not UNIQUE
                              which means
                              Lead_project_office_code,lead_project_team_code,org_id,rpt_date is not unique combination from your insert query...
                              This makes perfect sense because
                              in the group by
                              GROUP BY Duration,
                                        Num_Days,
                                        Num_Hours,
                                        Num_Minutes,
                                        Num_Seconds,
                                        Lead_Project_Office_Code,
                                        Lead_Project_Team_Code,
                                        Org_Id,
                                        Rpt_Date,
                                        Rpt_Date_Wid,
                                        Local_Curr_Code,
                                        Usd_Exch_Rate,
                                        Eur_Exch_Rate,
                                        Gbp_Exch_Rate
                              it only tell by those column combination, result will be unique...
                              In order to solve this. you either need to take out columns from your group by and add analytic function around these columns or you have to discard the unique index constrain
                              • 27. Re: ORA-01427:single-row sub query returns more than one row (group by)
                                994691
                                So Analytical function means, I think we need to re-write the complete query again. If we remove the indexes then duplicates are going to include. Is there any other way other than analytic functions. I am geting 864 extra rows when I try "Having count(*)>1. but when I run the query it is rejecting the coulmns more than 864 , . why it is rejecting even though the other columns are unique. Thanks
                                • 28. Re: ORA-01427:single-row sub query returns more than one row (group by)
                                  989009
                                  I do not know how to explain it to you... You need to understand what is 'group by' mean...

                                  Following code will fit in your table... but if you noticed.. I added max() function around the columns I took out from group by clause... you may want to change that to sum()/count()/min() etc... those are called analytic function...

                                  There is NO OTHER WAY to get unique value on those four columns with other columns in 'group by'...
                                  SELECT SUM(Wip_To_Bill_Loc_Amt) AS Wip_To_Bill_Loc_Amt,
                                         SUM(Realization_Loc_Amt) AS Realization_Loc_Amt,
                                         SUM(Neg_Real_Loc_Amt) AS Neg_Real_Loc_Amt,
                                         SUM(Pos_Real_Loc_Amt) AS Pos_Real_Loc_Amt,
                                         SUM(Bill_In_Advance_Loc_Amt) AS Bill_In_Advance_Loc_Amt,
                                         SUM(Carry_Forward_Loc_Amt) AS Carry_Forward_Loc_Amt,
                                         SUM(Bill_To_Client_Loc_Amt) AS Bill_To_Client_Loc_Amt,
                                         SUM(Remain_Wip_To_Bill_Loc_Amt) AS Remain_Wip_To_Bill_Loc_Amt,
                                         SUM(Ar_Inv_Amt) AS Ar_Inv_Amt,
                                         SUM(Ar_Tax_Amt) AS Ar_Tax_Amt,
                                         COUNT(DISTINCT CASE
                                                 WHEN ((Inv_Status_Type = 'FIN' AND
                                                      Inv_Status_Code NOT IN ('COMPLETE', 'PROCESSED')) OR
                                                      (Inv_Status_Type = 'WS' AND Inv_Status_Code NOT IN ('PRC'))) --COMPLETED
                                                      AND To_Char((SYSDATE - 5), 'YYYYMMDD') BETWEEN
                                                      To_Char(Status_Start_Dt, 'YYYYMMDD') AND
                                                      To_Char(Status_End_Dt, 'YYYYMMDD') THEN
                                                  Rpad(Integration_Id, 32)
                                               END) AS Beg_Bal_Wkst_Num,
                                         COUNT(DISTINCT CASE
                                                 WHEN (Inv_Status_Code = 'NEW') AND
                                                      To_Char((SYSDATE - 4), 'YYYYMMDD') BETWEEN
                                                      To_Char(Status_Start_Dt, 'YYYYMMDD') AND
                                                      To_Char(Status_End_Dt, 'YYYYMMDD') THEN
                                                  Rpad(Integration_Id, 32)
                                               END) AS Wkst_Received_Num,
                                         COUNT(DISTINCT CASE
                                                 WHEN ((Inv_Status_Type = 'FIN' AND
                                                      Inv_Status_Code IN ('COMPLETE', 'PROCESSED')) OR
                                                      (Inv_Status_Type = 'WS' AND Inv_Status_Code IN ('PRC'))) --COMPLETED
                                                      AND To_Char((SYSDATE - 4), 'YYYYMMDD') BETWEEN
                                                      To_Char((Status_Start_Dt), 'YYYYMMDD') AND
                                                      To_Char((Status_End_Dt), 'YYYYMMDD') THEN
                                                  Rpad(Integration_Id, 32)
                                               END) AS Wkst_Processed_Num,
                                         COUNT(DISTINCT CASE
                                                 WHEN (Inv_Status_Type = 'FIN' AND Inv_Status_Code = 'CANCELLED') AND
                                                      To_Char((SYSDATE - 4), 'YYYYMMDD') BETWEEN
                                                      To_Char((Status_Start_Dt), 'YYYYMMDD') AND
                                                      To_Char((Status_End_Dt), 'YYYYMMDD') THEN
                                                  Rpad(Integration_Id, 32)
                                               END) AS Wkst_Canceled_Num,
                                         max(Duration) AS Duration,
                                         max(Num_Days) AS Num_Days,
                                         max(Num_Hours) AS Num_Hours,
                                         max(Num_Minutes) AS Num_Minutes,
                                         max(Num_Seconds) AS Num_Seconds,
                                         Lead_Project_Office_Code AS Lead_Project_Office_Code,
                                         Lead_Project_Team_Code AS Lead_Project_Team_Code,
                                         Org_Id AS Org_Id,
                                         Trunc(SYSDATE - 1) AS Rpt_Date,
                                         max((To_Char((SYSDATE - 1), 'YYYYMMDD')) AS Rpt_Date_Wid,
                                         --last_day(a.report_date) mth_end_dt,
                                         max(Loc_Curr_Code) AS Local_Curr_Code,
                                         max(Usd_Exch_Rate) AS Usd_Exch_Rate,
                                         max(Eur_Exch_Rate) AS Eur_Exch_Rate,
                                         max(Gbp_Exch_Rate) AS Gbp_Exch_Rate
                                    FROM Wc_Twfs_Olb_Invoice_History_f
                                   WHERE Rpt_Dt_Mcal_Period_Wid =
                                         (SELECT MAX(Rpt_Dt_Mcal_Period_Wid)
                                            FROM Wc_Twfs_Olb_Invoice_History_f)
                                   GROUP BY 
                                            Lead_Project_Office_Code,
                                            Lead_Project_Team_Code,
                                            Org_Id,
                                            Rpt_Date
                                  order by Beg_Bal_Wkst_Num, Wkst_Received_Num, Wkst_Processed_Num, Wkst_Canceled_Num DESC;
                                  • 29. Re: ORA-01427:single-row sub query returns more than one row (group by)
                                    994691
                                    I am using this query but still getting multiple duplicate values

                                    SELECT SUM(Wip_To_Bill_Loc_Amt) AS Wip_To_Bill_Loc_Amt,
                                    SUM(Realization_Loc_Amt) AS Realization_Loc_Amt,
                                    SUM(Neg_Real_Loc_Amt) AS Neg_Real_Loc_Amt,
                                    SUM(Pos_Real_Loc_Amt) AS Pos_Real_Loc_Amt,
                                    SUM(Bill_In_Advance_Loc_Amt) AS Bill_In_Advance_Loc_Amt,
                                    SUM(Carry_Forward_Loc_Amt) AS Carry_Forward_Loc_Amt,
                                    SUM(Bill_To_Client_Loc_Amt) AS Bill_To_Client_Loc_Amt,
                                    SUM(Remain_Wip_To_Bill_Loc_Amt) AS Remain_Wip_To_Bill_Loc_Amt,
                                    SUM(Ar_Inv_Amt) AS Ar_Inv_Amt,
                                    SUM(Ar_Tax_Amt) AS Ar_Tax_Amt,
                                    COUNT(DISTINCT CASE
                                    WHEN ((Inv_Status_Type = 'FIN' AND
                                    Inv_Status_Code NOT IN ('COMPLETE', 'PROCESSED')) OR
                                    (Inv_Status_Type = 'WS' AND Inv_Status_Code NOT IN ('COMPLETE'))) --COMPLETED
                                    AND To_Char((SYSDATE - 19), 'YYYYMMDD') BETWEEN
                                    To_Char(Status_Start_Dt, 'YYYYMMDD') AND
                                    To_Char(Status_End_Dt, 'YYYYMMDD') THEN
                                    olb_inv_row_wid
                                    END) AS Beg_Bal_Wkst_Num,

                                    COUNT(DISTINCT CASE
                                    WHEN (Inv_Status_Code IN ('NEW','New'))
                                    AND To_Char((SYSDATE - 18), 'YYYYMMDD') BETWEEN
                                    To_Char(Status_Start_Dt, 'YYYYMMDD') AND
                                    To_Char(Status_End_Dt, 'YYYYMMDD') THEN
                                    olb_inv_row_wid
                                    END) AS WKST_RECEIVED_NUM,
                                    COUNT(DISTINCT CASE
                                    WHEN ((Inv_Status_Type = 'FIN' AND
                                    Inv_Status_Code IN ('COMPLETE', 'PROCESSED')) OR
                                    (Inv_Status_Type = 'WS' AND Inv_Status_Code IN ('COMPLETE')))
                                    AND To_Char((SYSDATE - 18), 'YYYYMMDD') BETWEEN
                                    To_Char(Status_Start_Dt, 'YYYYMMDD') AND
                                    To_Char(Status_End_Dt, 'YYYYMMDD') THEN
                                    olb_inv_row_wid
                                    END) AS WKST_PROCESSED_NUM,
                                    COUNT(DISTINCT CASE
                                    WHEN ((Inv_Status_Type = 'FIN' AND
                                    Inv_Status_Code IN ('CANCELED')))
                                    AND To_Char((SYSDATE - 18), 'YYYYMMDD') BETWEEN
                                    To_Char(Status_Start_Dt, 'YYYYMMDD') AND
                                    To_Char(Status_End_Dt, 'YYYYMMDD') THEN
                                    olb_inv_row_wid
                                    END) AS WKST_CANCELED_NUM,
                                    sum(Duration) AS Duration,
                                    sum(Num_Days) AS Num_Days,
                                    sum(Num_Hours) AS Num_Hours,
                                    sum(Num_Minutes) AS Num_Minutes,
                                    sum(Num_Seconds) AS Num_Seconds,
                                    Lead_Project_Office_Code AS Lead_Project_Office_Code,
                                    Lead_Project_Team_Code AS Lead_Project_Team_Code,
                                    Org_Id AS Org_Id,
                                    Trunc(SYSDATE - 1) AS Rpt_Date,
                                    To_Char((SYSDATE - 1), 'YYYYMMDD') AS Rpt_Date_Wid,
                                    --last_day(a.report_date) mth_end_dt,
                                    max(Loc_Curr_Code) AS Local_Curr_Code,
                                    sum(Usd_Exch_Rate) AS Usd_Exch_Rate,
                                    sum(Eur_Exch_Rate) AS Eur_Exch_Rate,
                                    sum(Gbp_Exch_Rate) AS Gbp_Exch_Rate
                                    FROM Wc_Twfs_Olb_Invoice_History_f
                                    WHERE Rpt_Dt_Mcal_Period_Wid =
                                    (SELECT MAX(Rpt_Dt_Mcal_Period_Wid)
                                    FROM Wc_Twfs_Olb_Invoice_History_f)
                                    GROUP BY
                                    Lead_Project_Office_Code,
                                    Lead_Project_Team_Code,
                                    Org_Id,
                                    Trunc(SYSDATE - 1) having count(*)>1
                                    order by Beg_Bal_Wkst_Num, Wkst_Received_Num, Wkst_Processed_Num, Wkst_Canceled_Num DESC;



                                    Duration Num_days Num_Hours Num_Minutes Num_Seconds Lead_project_office_code lead_project_team_code
                                    19854     0     5 29     114     ARL1     000162
                                    43998     0     11     73     18     TOR2     000602
                                    43124     0     11     57     104     ARL1     000701
                                    1181641     13     16     14     1     ATL2     000254
                                    5297      0     1     28     17     ATL2     000260
                                    341821     3     22     56      61     ATL2     000604