1 2 3 Previous Next 36 Replies Latest reply: Mar 11, 2013 10:55 AM by 994691 RSS

    ORA-01427:single-row sub query returns more than one row (group by)

    994691
      Hello every one, I am very new to this field , and Right now I am working with this sql, where BEG_BAL_WKST,WKST_RECEIVED_NUM,WKST_PROCESSED_NUM,WKST_CANCELED_NUM are needs to be grouped by,but I am getting the "single-row sub query returns more than one row".

      This is the query I am using in my source qualifier:

      select
      SUM(tmp.WIP_TO_BILL_LOC_AMT) AS WIP_TO_BILL_LOC_AMT,
      sum(tmp.REALIZATION_LOC_AMT) AS REALIZATION_LOC_AMT,
      SUM(tmp.NEG_REAL_LOC_AMT) AS NEG_REAL_LOC_AMT,
      sum(tmp.POS_REAL_LOC_AMT) AS POS_REAL_LOC_AMT,
      sum(tmp.BILL_IN_ADVANCE_LOC_AMT) AS BILL_IN_ADVANCE_LOC_AMT,
      sum(tmp.CARRY_FORWARD_LOC_AMT) AS CARRY_FORWARD_LOC_AMT,
      sum(tmp.BILL_TO_CLIENT_LOC_AMT) AS BILL_TO_CLIENT_LOC_AMT,
      sum(tmp.REMAIN_WIP_TO_BILL_LOC_AMT) REMAIN_WIP_TO_BILL_LOC_AMT,
      sum(tmp.AR_INV_AMT) AS AR_INV_AMT,
      sum(tmp.AR_TAX_AMT) AS AR_TAX_AMT,
      tmp.BEG_BAL_WKST_NUM AS BEG_BAL_WKST_NUM,
      tmp.WKST_RECEIVED_NUM AS WKST_RECEIVED_NUM,
      tmp.WKST_PROCESSED_NUM AS WKST_PROCESSED_NUM,
      tmp.WKST_CANCELED_NUM AS WKST_CANCELED_NUM,
      tmp.DURATION AS DURATION,
      tmp.NUM_DAYS AS NUM_DAYS,
      tmp.NUM_HOURS AS NUM_HOURS,
      tmp.NUM_MINUTES AS NUM_MINUTES,
      tmp.NUM_SECONDS AS NUM_SECONDS,
      tmp.LEAD_PROJECT_OFFICE_CODE AS LEAD_PROJECT_OFFICE_CODE,
      tmp.LEAD_PROJECT_TEAM_CODE AS LEAD_PROJECT_TEAM_CODE,
      tmp.ORG_ID AS ORG_ID,
      tmp.RPT_DATE AS RPT_DATE,
      tmp.RPT_DATE_WID AS RPT_DATE_WID,
      tmp.LOCAL_CURR_CODE AS LOCAL_CURR_CODE,
      tmp.USD_EXCH_RATE AS USD_EXCH_RATE,
      tmp.EUR_EXCH_RATE AS EUR_EXCH_RATE,
      tmp.GBP_EXCH_RATE AS GBP_EXCH_RATE
      from(
      SELECT
      WIP_TO_BILL_LOC_AMT as WIP_TO_BILL_LOC_AMT ,
      REALIZATION_LOC_AMT AS REALIZATION_LOC_AMT,
      NEG_REAL_LOC_AMT AS NEG_REAL_LOC_AMT ,
      POS_REAL_LOC_AMT AS POS_REAL_LOC_AMT,
      BILL_IN_ADVANCE_LOC_AMT AS BILL_IN_ADVANCE_LOC_AMT ,
      CARRY_FORWARD_loc_AMT AS CARRY_FORWARD_LOC_AMT,
      bill_to_client_LOC_AMT AS BILL_TO_CLIENT_LOC_AMT ,
      REMAIN_WIP_TO_BILL_LOC_AMT AS REMAIN_WIP_TO_BILL_LOC_AMT,
      AR_inv_AMT AS AR_INV_AMT,
      ar_tax_amt AS AR_TAX_AMT,
      (SELECT count(distinct(RPAD(INTEGRATION_ID,32)))
      FROM wc_twfs_olb_invoice_history_f
      WHERE ((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')group by rpad(integration_id,32)) AS BEG_BAL_WKST_NUM ,
      (SELECT count(distinct(RPAD(INTEGRATION_ID,32)))
      FROM wc_twfs_olb_invoice_history_f
      WHERE (inv_status_code='NEW')
      AND to_char((sysdate-4),'YYYYMMDD') between to_char(status_start_dt,'YYYYMMDD') and to_char(status_end_dt,'YYYYMMDD')group by rpad(integration_id,32))AS WKST_RECEIVED_NUM ,
      (SELECT count(distinct(RPAD(INTEGRATION_ID,32)))
      FROM wc_twfs_olb_invoice_history_f
      WHERE ((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')group by rpad(integration_id,32))AS WKST_PROCESSED_NUM ,
      (SELECT count(distinct(RPAD(INTEGRATION_ID,32)))
      FROM wc_twfs_olb_invoice_history_f
      WHERE (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')group by rpad(integration_id,32)) 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))tmp
      group by BEG_BAL_WKST_NUM,WKST_RECEIVED_NUM,WKST_PROCESSED_NUM,WKST_CANCELED_NUM,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;

      Can you please suggest me what to do next, and what would be the solution to this.
      Thanks a lot in advance. please show me some direction.
        • 1. Re: ORA-01427:single-row sub query returns more than one row (group by)
          989009
          SELECT COUNT(DISTINCT(Rpad(Integration_Id, 32)))
          FROM Wc_Twfs_Olb_Invoice_History_f
          WHERE ((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')
          GROUP BY Rpad(Integration_Id, 32)


          your sub select would give you more than one row...
          you may want to get rid of the group by down there
          • 2. Re: ORA-01427:single-row sub query returns more than one row (group by)
            994691
            yeah but if i am removing the group by from the inner coulmns then the query is running but it is giving the same values for all the rows

            eg:- if the count of beg_bal_wkst_num is 20140, then it is giving

            Beg_bal_wkst_num
            20140
            20140
            20140
            20140
            so on....

            but this mean those columns are not grouped and t is returing the same value to all the coulmns. I am trying to change queries but it is not working, All I am looking for is, The query should be runnable and also the count for those four columns(beg_bal_wkst_num,wkst_received_num,wkst_processed_num,wkst_canceled_num) should be groped in this query,

            Let me know if there is any other way that I can do it.
            Thanks alot
            • 3. Re: ORA-01427:single-row sub query returns more than one row (group by)
              Frank Kulash
              Hi,

              Welcome to the forum!

              Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved, so that the people who want to help you can re-create the problem and test their ideas.
              Also post the results you want from that data, and an explanation of how you get those results from that data, with specific examples.
              Simplify the problem as much as possible. Remove all tables and columns that play no role in this problem. Leave only the part that you don't already know how to do.
              Always say which version of Oracle you're using (for example, 11.2.0.2.0).
              See the forum FAQ {message:id=9360002}


              You need to guarantee that the scalar sub-queries never return more than 1 row. As mentioned before, using an aggregate function (such as COUNT) with<b>OUT</b> a GROUP BY clause guarantees that the sub-query will return exactly one row. You can correlate the sub-query to the main query so that the sub-query will return different values on different calls.

              As an example of a correlated sub-query, consider this query on the scott.dept table (which I assume exists on your system). For every department, we want to see how many distinct hiredates in the scott.emp table are related. Here's how to do that:
              SELECT  d.*
              ,     (
                       SELECT  COUNT (DISTINCT hiredate)
                       FROM    scott.emp
                       WHERE   deptno     = d.deptno     -- Correlated condition
                   )     AS job_cnt
              FROM     scott.dept  d
              ;
              Output:
              `   DEPTNO DNAME          LOC              JOB_CNT
              ---------- -------------- ------------- ----------
                      10 ACCOUNTING     NEW YORK               3
                      20 RESEARCH       DALLAS                 5
                      30 SALES          CHICAGO                6
                      40 OPERATIONS     BOSTON                 0
              I suspect you need to do something like this in your query.

               

              By the way, you can compare DATEs in Oracle; you don't need to convert them to some other data type.
              So instead of
              AND to_char((sysdate-4),'YYYYMMDD') between to_char((status_start_dt),'YYYYMMDD') and to_char((status_end_dt),'YYYYMMDD')
              it would be simpler and more efficient to say:
              AND     status_start_dt  <  TRUNC (SYSDATE) - 3
              AND     status_end_dt      >= TRUNC (SYSDATE) - 4
              I'm assuming that status_end_dt can't be earlier than status_start_dt.
              • 4. Re: ORA-01427:single-row sub query returns more than one row (group by)
                Warren Tolentino
                -- WKST_RECEIVED_NUM
                SELECT count(distinct(RPAD(INTEGRATION_ID,32))) 
                  FROM wc_twfs_olb_invoice_history_f 
                 WHERE (inv_status_code = 'NEW')
                   AND to_char((sysdate-4),'YYYYMMDD') between to_char(status_start_dt,'YYYYMMDD') and to_char(status_end_dt,'YYYYMMDD')
                group by rpad(integration_id,32)
                
                -- WKST_PROCESSED_NUM
                SELECT count(distinct(RPAD(INTEGRATION_ID,32))) 
                  FROM wc_twfs_olb_invoice_history_f 
                  WHERE ((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')
                group by rpad(integration_id,32)
                
                -- WKST_CANCELED_NUM
                SELECT count(distinct(RPAD(INTEGRATION_ID,32)))
                  FROM wc_twfs_olb_invoice_history_f 
                 WHERE (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')
                group by rpad(integration_id,32)
                your inline view from one of the above examples could have returning more that one row. try to run that query and post the result so we can which query returns more than one row.
                • 5. Re: ORA-01427:single-row sub query returns more than one row (group by)
                  994691
                  Hi Thank you for your response,

                  SELECT D.*,
                  (SELECT count(distinct(olb_inv_row_wid ))
                  FROM wc_twfs_olb_invoice_history_f
                  WHERE ((inv_status_type in ('FIN')AND inv_status_code NOT IN ('COMPLETE','PROCESSED'))
                  OR (inv_status_type in ('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')) AS BEG_BAL_WKST_NUM ,
                  (SELECT count(distinct(olb_inv_row_wid ))
                  FROM wc_twfs_olb_invoice_history_f
                  WHERE (inv_status_code IN ('NEW'))
                  AND to_char((sysdate-4),'YYYYMMDD') between to_char(status_start_dt,'YYYYMMDD') and to_char(status_end_dt,'YYYYMMDD'))AS WKST_RECEIVED_NUM ,
                  (SELECT count(distinct(olb_inv_row_wid ))
                  FROM wc_twfs_olb_invoice_history_f
                  WHERE ((inv_status_type IN ('FIN') and inv_status_code IN ('COMPLETE','PROCESSED'))
                  OR (inv_status_type IN ('WS') AND inv_status_code IN ('COMPLETED'))) --COMPLETED
                  AND to_char((sysdate-4),'YYYYMMDD') between to_char((status_start_dt),'YYYYMMDD') and to_char((status_end_dt),'YYYYMMDD'))AS WKST_PROCESSED_NUM ,
                  (SELECT count(distinct(olb_inv_row_wid ))
                  FROM wc_twfs_olb_invoice_history_f
                  WHERE (inv_status_type IN ('FIN') AND inv_status_code IN ('CANCELLED'))
                  AND to_char((sysdate-4),'YYYYMMDD') between to_char((status_start_dt),'YYYYMMDD') and to_char((status_end_dt),'YYYYMMDD')) AS WKST_CANCELED_NUM
                  FROM wc_twfs_olb_invoice_history_f D WHERE RPT_DT_MCAL_PERIOD_WID=(SELECT MAX(RPT_DT_MCAL_PERIOD_WID) FROM WC_TWFS_OLB_INVOICE_HISTORY_F);

                  I have tried the same, but the out put is still not a group by.

                  Beg_bal_wkst_num wkst_received_num wkst_processed_num wkst_canceled_num
                  22060 16924 4322 0
                  22060 16924 4322 0
                  22060 16924 4322 0
                  It is returning the same values to all rows .
                  • 6. Re: ORA-01427:single-row sub query returns more than one row (group by)
                    989009
                    you have to move the select count to upper level select....
                    It will repeat the same value for sure since in the inner select, you already chose to have the count fixed for every rows...
                    • 7. Re: ORA-01427:single-row sub query returns more than one row (group by)
                      989009
                      you may want to change it something like
                      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,
                             /*(SELECT COUNT(DISTINCT(Rpad(Integration_Id, 32)))
                                FROM Wc_Twfs_Olb_Invoice_History_f
                               WHERE ((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')
                               GROUP BY Rpad(Integration_Id, 32)) AS Beg_Bal_Wkst_Num,
                             (SELECT COUNT(DISTINCT(Rpad(Integration_Id, 32)))
                                FROM Wc_Twfs_Olb_Invoice_History_f
                               WHERE (Inv_Status_Code = 'NEW')
                                 AND To_Char((SYSDATE - 4), 'YYYYMMDD') BETWEEN
                                     To_Char(Status_Start_Dt, 'YYYYMMDD') AND
                                     To_Char(Status_End_Dt, 'YYYYMMDD')
                               GROUP BY Rpad(Integration_Id, 32)) AS Wkst_Received_Num,
                             (SELECT COUNT(DISTINCT(Rpad(Integration_Id, 32)))
                                FROM Wc_Twfs_Olb_Invoice_History_f
                               WHERE ((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')
                               GROUP BY Rpad(Integration_Id, 32)) AS Wkst_Processed_Num,
                             (SELECT COUNT(DISTINCT(Rpad(Integration_Id, 32)))
                                FROM Wc_Twfs_Olb_Invoice_History_f
                               WHERE (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')
                               GROUP BY Rpad(Integration_Id, 32)) 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 Beg_Bal_Wkst_Num,
                                Wkst_Received_Num,
                                Wkst_Processed_Num,
                                Wkst_Canceled_Num,
                                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;
                      Edited by: 986006 on Mar 4, 2013 1:08 PM
                      • 8. Re: ORA-01427:single-row sub query returns more than one row (group by)
                        994691
                        Thank you for responding.

                        But it is not allowing those 4 columns to be group by. It is giving wkst_canceled_num .invalid identifier

                        If I remove that , then it is giving wkst_processed_num. invalid identifier.

                        It is not running.
                        Those columns are not grouping and the code is not running.
                        Thanks.
                        • 9. Re: ORA-01427:single-row sub query returns more than one row (group by)
                          994691
                          When I run the query it is giving the same error.
                          I am able to run the query individualy, but not all at a time.
                          • 10. Re: ORA-01427:single-row sub query returns more than one row (group by)
                            989009
                            forget about your grouping for now... run following code...
                            lets get one of the row working first ...
                            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;
                            • 11. Re: ORA-01427:single-row sub query returns more than one row (group by)
                              994691
                              Yes this is running ,but it is returning all zero's in the beg_bal_wkst_num
                              • 12. Re: ORA-01427:single-row sub query returns more than one row (group by)
                                989009
                                What following two selects would give you?
                                SELECT 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)
                                  FROM Wc_Twfs_Olb_Invoice_History_f;
                                SELECT COUNT(DISTINCT(Rpad(Integration_Id, 32)))
                                  FROM Wc_Twfs_Olb_Invoice_History_f
                                 WHERE ((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');
                                • 13. Re: ORA-01427:single-row sub query returns more than one row (group by)
                                  994691
                                  Previously it used to give
                                  26382 as count for beg_bal_wkst_num,
                                  16932 for wkst_received_num
                                  4322 for wkst_processed_num and
                                  0 for wkst_canceled_num

                                  Now I checked for different sysdate's but they are returning all 0's. might be there is some changes made to wc_twfs_olb_invoice_history_f
                                  • 14. Re: ORA-01427:single-row sub query returns more than one row (group by)
                                    994691
                                    SELECT 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
                                    olb_inv_row_wid
                                    END)
                                    FROM Wc_Twfs_Olb_Invoice_History_f; output is 26382

                                    SELECT COUNT(DISTINCT(olb_inv_row_wid))
                                    FROM Wc_Twfs_Olb_Invoice_History_f
                                    WHERE ((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'); output is 26382

                                    In both the cases it is returnd 26382
                                    1 2 3 Previous Next