This discussion is archived
1 2 3 Previous Next 36 Replies Latest reply: Mar 11, 2013 8:55 AM by 994691 Go to original post RSS
  • 30. Re: ORA-01427:single-row sub query returns more than one row (group by)
    989009 Newbie
    Currently Being Moderated
    1, The query I provided doesnt have 'having count(*) > 1'
    2, I didnt see any duplicate rows from the data you provided
  • 31. Re: ORA-01427:single-row sub query returns more than one row (group by)
    994691 Newbie
    Currently Being Moderated
    The data I provides is showing 141 in the num_seconds, where the number after 59 should increase a count in Num_minutes, But it is showing 141 which is the values are plotting wrong, and also when I use Duration,Num_Days,Num_Hours,Num_minutes,num_seconds in group by then I am getting the values correct in these columns.
    But the only thing I am confused is , I am still getting the duplicate rows in both the cases.
  • 32. Re: ORA-01427:single-row sub query returns more than one row (group by)
    994691 Newbie
    Currently Being Moderated
    I am getting duplicate rows in source query only.

    Because when I ran this query
    select org_id||Lead_Project_Office_Code||lead_project_team_code||Trunc(SYSDATE - 1)
    from wc_twfs_olb_invoice_history_f where not exists( select org_id||Lead_Project_Office_Code||lead_project_team_code||Trunc(SYSDATE - 1) from
    wc_twfs_olb_aggr_proc_inv_f);
    It returned nothing 0 rows

    When I ran this Query
    select org_id||Lead_Project_Office_Code||lead_project_team_code||Trunc(SYSDATE - 1)
    from wc_twfs_olb_invoice_history_f where exists( select org_id||Lead_Project_Office_Code||lead_project_team_code||Trunc(SYSDATE - 1) from
    wc_twfs_olb_aggr_proc_inv_f);
    It returned 344294 rows

    when I ran this query
    select org_id||Lead_Project_Office_Code||lead_project_team_code||Trunc(SYSDATE - 1)
    from wc_twfs_olb_aggr_proc_inv_f where exists( select org_id||Lead_Project_Office_Code||lead_project_team_code||Trunc(SYSDATE - 1) from
    wc_twfs_olb_invoice_history_f);
    It returned 1235 rows.

    where wc_twfs_olb_invoice_history_f is my source
    and wc_twfs_olb_aggr_proc_inv_f is my target table

    Can any one help me in eliminating the duplicate rows. Thanks a lot Y.L, your support is really appreciable. let me know till I am getting the duplicate values.
  • 33. Re: ORA-01427:single-row sub query returns more than one row (group by)
    989009 Newbie
    Currently Being Moderated
    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
    The data I provides is showing 141 in the num_seconds, where the number after 59 should increase a count in Num_minutes
    i cant find the numbers you were talking about... I am not talking about data validation here... this only gives you unique records that fits your table constrain...
    when I use Duration,Num_Days,Num_Hours,Num_minutes,num_seconds in group by then I am getting the values correct in these columns.
    Then you cant have unique constrain on the table for the four columns...

    for example(I made up the number but that tell what was happening)
    Duration Num_days Num_Hours Num_Minutes Num_Seconds Lead_project_office_code lead_project_team_code org_id
    19854     0        5               29                     114               ARL1                           000162                         1
    43998     0        11                 73                     18               ARL1                           000162                         1
    when you do the group by on all those columns... it will should you both lines(which gives you the 'correct' data you want but there is no way to merge those two line into one which violate the constrain on your table)

    when take the rest columns out of group by.. it will give you unique rows(which suits your constrain but you have to do a sum/max/min function on the columns you took out so oracle knows how to merge the rows...)
    Duration Num_days Num_Hours Num_Minutes Num_Seconds Lead_project_office_code lead_project_team_code org_id
    6789      0             16              102            132               ARL1                           000162                       1
  • 34. Re: ORA-01427:single-row sub query returns more than one row (group by)
    989009 Newbie
    Currently Being Moderated
    select org_id||Lead_Project_Office_Code||lead_project_team_code||Trunc(SYSDATE - 1)
    from wc_twfs_olb_invoice_history_f where not exists( select org_id||Lead_Project_Office_Code||lead_project_team_code||Trunc(SYSDATE - 1) from
    wc_twfs_olb_aggr_proc_inv_f);
    It returned nothing 0 rows
    This basically means every org_id||Lead_Project_Office_Code||lead_project_team_code||Trunc(SYSDATE - 1) combination in your source table have been copied over to your target table
    When I ran this Query
    select org_id||Lead_Project_Office_Code||lead_project_team_code||Trunc(SYSDATE - 1)
    from wc_twfs_olb_invoice_history_f where exists( select org_id||Lead_Project_Office_Code||lead_project_team_code||Trunc(SYSDATE - 1) from
    wc_twfs_olb_aggr_proc_inv_f);
    It returned 344294 rows
    This means before you do the aggregation on these org_id||Lead_Project_Office_Code||lead_project_team_code||Trunc(SYSDATE - 1) columns.. there are 344294 rows need to be aggregated....(refers to my example that two or more lines have the same org_id, Lead_Project_Office_Code, lead_project_team_code but different number_days, number_mins, number_seconds)
    when I ran this query
    select org_id||Lead_Project_Office_Code||lead_project_team_code||Trunc(SYSDATE - 1)
    from wc_twfs_olb_aggr_proc_inv_f where exists( select org_id||Lead_Project_Office_Code||lead_project_team_code||Trunc(SYSDATE - 1) from
    wc_twfs_olb_invoice_history_f);
    It returned 1235 rows. 
    This means there are 1235 unique combinations for org_id||Lead_Project_Office_Code||lead_project_team_code||Trunc(SYSDATE - 1)
    actually you do not need to have Trunc(SYSDATE - 1) into your group by because that will always be the day before...
  • 35. Re: ORA-01427:single-row sub query returns more than one row (group by)
    994691 Newbie
    Currently Being Moderated
    Hi Y.L, Thanks alot. I did the same thing and now I am getting the unique rows.
    both my source and targets are getting 1235 rows. This helped me.
  • 36. Re: ORA-01427:single-row sub query returns more than one row (group by)
    994691 Newbie
    Currently Being Moderated
    Hi,

    I have a small doubt,

    If I wanted to use yesterday's date , I can use sysdate-1, If I am using a hardcode value like'20130217 23:59:59' then can I use '(sysdate-22) 23:59:59' will both are same and gives the same result.

    For ex:-
    COUNT( CASE
    WHEN '20130215 23:59:59' between to_char(status_start_dt,'YYYYMMDD HH:MM:SS') and to_char(status_end_dt,'YYYYMMDD HH:MM:SS' )
    and Inv_status_code not in ('COMPLETE','PROCESSED','COMPLETED', 'Processed')
    THEN
    olb_inv_row_wid
    END) AS Beg_Bal_Wkst_Num,
    I am hardcoded the part '20130215 23:59:59' but here the time will be constant, and only the date need to be taking is sysdate-2 in place of 20130215. Can you please suggest me.

    Thanks.
1 2 3 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points