1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

ORA-00937: not a single-group group function

Discussion in 'SQL PL/SQL' started by lavanyamc, May 12, 2012.

  1. lavanyamc

    lavanyamc Guest

    dear All..below am executed with out opp.opportunity_number
    ,ppf.full_name Manager_name
    ,bg.name company_name then i got a result ..but i want display also opporunity_number and name


    Code (Text):
    SELECT [B][COLOR="red"]opp.opportunity_number
           ,ppf.full_name  Manager_name
           ,bg.name company_name
    ,[/COLOR][/B]sum(Case when current_status = 'L0'and POSITION = 'WIN'  THEN CASE
                                                                                    when sum(PARTNER_AWA) IS NOT NULL THEN sum(PARTNER_AWA)
                                                                                    when sum(PARTNER_POP) IS NOT NULL THEN sum(PARTNER_POP)
                                                                                    when sum(PARTNER_EST) IS NOT NULL THEN sum(PARTNER_EST)
                                                                                    when sum(PARTNER_POT) IS NOT NULL THEN sum(PARTNER_POT)
                                                                                   END  END) AS WIN_VALUE
                                                                                   
     ,sum(Case when current_status = 'L1'and POSITION = 'Best Few'  THEN CASE
                                                                                    when sum(PARTNER_AWA) IS NOT NULL THEN sum(PARTNER_AWA)
                                                                                    when sum(PARTNER_POP) IS NOT NULL THEN sum(PARTNER_POP)
                                                                                    when sum(PARTNER_EST) IS NOT NULL THEN sum(PARTNER_EST)
                                                                                    when sum(PARTNER_POT) IS NOT NULL THEN sum(PARTNER_POT)
                                                                                 END  END) AS BF_VALUE
                                                                                                                                                  ,COUNT(Case when current_status = 'L0'and POSITION = 'WIN'  THEN  opp.OPPORTUNITY_NUMBER END)  AS cases_win
    ,count(Case when  current_status = 'L1'and POSITION = 'Best Few'  THEN  opp.OPPORTUNITY_NUMBER END ) as cases_BF
    ,count(Case when  current_status in ('L2','L3') and POSITION = 'In Funnel'  THEN  opp.OPPORTUNITY_NUMBER END) as cases_IF
    ,count(Case when  current_status = 'L4'and POSITION = 'Out Funnel'  THEN  opp.OPPORTUNITY_NUMBER END) as cases_OF
     from XXABC OPp
         ,xxdet a,
         per_all_people_f ppf,
         fnd_user fnd,
         per_business_groups bg
    WHERE a.OPPORTUNITY_NUMBER = OPP.OPPORTUNITY_NUMBER
    and a.VERSION = OPP.VERSION
    AND fnd.user_id = opp.created_by
    AND ppf.person_id = fnd.EMPLOYEE_ID
    --AND ppf.person_id = NVL(:p_person_id,ppf.person_id)
    AND ppf.BUSINESS_GROUP_ID = bg.BUSINESS_GROUP_ID
    AND  a.VERSION IN (SELECT MAX(VERSION)
                        FROM XXABC c
                      WHERE  OPP.OPPORTUNITY_NUMBER = c.OPPORTUNITY_NUMBER)
    GROUP BY OPP.POSITION
             ,OPP.CURRENT_STATUS
             ,A.PARTNER_AWA
             ,A.PARTNER_EST
             ,A.PARTNER_POP
             ,A.PARTNER_POT
             ,opp.OPPORTUNITY_NUMBER
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This might be one way to do it:

    Code (SQL):
    SELECT x[COLOR=black].opportunity_number
    ,pf.full_name Manager_name
    ,b.name company_name
    ,x.win_value[/COLOR]
    ,x.bf_value
    ,x.cases_win
    ,x.cases_bf
    ,x.cases_if
    x.cases_of
    FROM
    (SELECT opp.opportunity_number,SUM(CASE WHEN current_status = 'L0'AND POSITION = 'WIN' THEN CASE
    WHEN SUM(PARTNER_AWA) IS NOT NULL THEN SUM(PARTNER_AWA)
    WHEN SUM(PARTNER_POP) IS NOT NULL THEN SUM(PARTNER_POP)
    WHEN SUM(PARTNER_EST) IS NOT NULL THEN SUM(PARTNER_EST)
    WHEN SUM(PARTNER_POT) IS NOT NULL THEN SUM(PARTNER_POT)
    END END) AS WIN_VALUE
    ,SUM(CASE WHEN current_status = 'L1'AND POSITION = 'Best Few' THEN CASE
    WHEN SUM(PARTNER_AWA) IS NOT NULL THEN SUM(PARTNER_AWA)
    WHEN SUM(PARTNER_POP) IS NOT NULL THEN SUM(PARTNER_POP)
    WHEN SUM(PARTNER_EST) IS NOT NULL THEN SUM(PARTNER_EST)
    WHEN SUM(PARTNER_POT) IS NOT NULL THEN SUM(PARTNER_POT)
    END END) AS BF_VALUE
    ,COUNT(CASE WHEN current_status = 'L0'AND POSITION = 'WIN' THEN opp.OPPORTUNITY_NUMBER END) AS cases_win
    ,COUNT(CASE WHEN current_status = 'L1'AND POSITION = 'Best Few' THEN opp.OPPORTUNITY_NUMBER END ) AS cases_BF
    ,COUNT(CASE WHEN current_status IN ('L2','L3') AND POSITION = 'In Funnel' THEN opp.OPPORTUNITY_NUMBER END) AS cases_IF
    ,COUNT(CASE WHEN current_status = 'L4'AND POSITION = 'Out Funnel' THEN opp.OPPORTUNITY_NUMBER END) AS cases_OF
    FROM XXABC OPp
    ,xxdet a,
    per_all_people_f ppf,
    fnd_user fnd,
    per_business_groups bg
    WHERE a.OPPORTUNITY_NUMBER = OPP.OPPORTUNITY_NUMBER
    AND a.VERSION = OPP.VERSION
    AND fnd.user_id = opp.created_by
    AND ppf.person_id = fnd.EMPLOYEE_ID
    --AND ppf.person_id = NVL(:p_person_id,ppf.person_id)
    AND ppf.BUSINESS_GROUP_ID = bg.BUSINESS_GROUP_ID
    AND a.VERSION IN (SELECT MAX(VERSION)
    FROM XXABC c
    WHERE OPP.OPPORTUNITY_NUMBER = c.OPPORTUNITY_NUMBER)
    GROUP BY OPP.POSITION
    ,OPP.CURRENT_STATUS
    ,A.PARTNER_AWA
    ,A.PARTNER_EST
    ,A.PARTNER_POP
    ,A.PARTNER_POT
    ,opp.OPPORTUNITY_NUMBER )x,
    xxabc op,
    xxdet a,
    per_all_people_f pf,
    per_business_group b,
    fnd_user f
    WHERE op.opportunity_number = x.opportunity_number
    AND a,opportunity_number = op.opportunity_number
    AND a.version = op.version
    AND f.user_id = op.created_by
    AND pf.person_id = f.employee_id
    /
     
     
    lavanyamc likes this.