This discussion is archived
1 2 Previous Next 19 Replies Latest reply: May 14, 2013 8:45 PM by 894936 RSS

not a group by expression

894936 Newbie
Currently Being Moderated
when i am running the below plsql block i am getting the error like not a group by expressiong.
can u please help me out where i made a mistake.
DECLARE
   CURSOR Cur_st
   IS
                          
SELECT DISTINCT
            CAST (A.STO_NO AS VARCHAR2 (5 CHAR)) AS BU_CODE,
            CAST ('STO' AS VARCHAR2 (3 CHAR)) AS BU_TYPE,
            CAST (NULL AS VARCHAR2 (7 BYTE)) AS CUST_NO,
            CAST (A.CUR_CODE AS VARCHAR2 (3 BYTE)) AS CUR_CODE,
            TO_DATE (A.SALES_DATE, 'YYMMDD'),
            CAST (A.RECEIPT_NO AS VARCHAR2 (10 BYTE)),
            CAST (A.CASH_NO AS VARCHAR2 (5 BYTE)) AS TILL_NO,
            CAST (NULL AS VARCHAR2 (2 BYTE)) AS CARD_NO,
            sold_amount AS INVOICE_TOTAL,
            CAST (NULL AS NUMBER (11, 2)) AS AMOUNT_ADVANCE_PAY,
            CAST (NULL AS NUMBER (11, 2)) AS AMOUNT_OF_GOODS,
            CAST (NULL AS NUMBER (11, 2)) AS AMOUNT_NON_GOODS,
            CAST (NULL AS NUMBER (11, 2)) AS AMOUNT_DISCOUNTS,
            'N' AS ON_HOLD_FLAG
       FROM I_0470002 A
   WHERE A.SOLD_AMOUNT != 0
   GROUP BY A.STO_NO,
            A.CUR_CODE,
            A.RECEIPT_NO,
            A.CASH_NO,
            A.SALES_DATE;
   v_comp_code   VARCHAR2 (10);
   v_sum         NUMBER;
   v_factor      NUMBER;
   v_sto_no      NUMBER;
   context       DBMS_XMLGEN.ctxtype;
   v_large       LONG;
   l_clob        CLOB;
BEGIN
   FOR Rec_st IN Cur_st
   LOOP

      batch_01_pck.setcompfromstore_prc (pi_str_stono => Rec_st.BU_CODE);
      /*dbms_output.put_line('Rec_st.BU_CODE-->'||Rec_st.BU_CODE);*/
      /*dbms_output.put_line('Rec_st.INVOICE_TOTAL-->'||Rec_st.INVOICE_TOTAL);*/

      v_comp_code := glob_01_pck.getcompcode_fct;
      /*dbms_output.put_line('v_comp_code-->'||v_comp_code);*/

      v_factor := curr_02_pck.corrfact_fct (v_comp_code, 'I0470001');
      /*dbms_output.put_line('v_factor-->'||v_factor);*/

      v_sum := rec_st.INVOICE_TOTAL* v_factor;
      /*dbms_output.put_line('v_sum-->'||v_sum);*/

      v_large :=
            'select sum(sold_amount)*'
         || v_factor
         || ' "INVOICE_TOTAL" ,sto_no as bu_code,"STO" as bu_type,null as cust_no,cur_code,RECEIPT_NO,TO_DATE (A.SALES_DATE,"YYMMDD")as sales_date,
cash_no as till_no,null as card_no,null as amount_advance_pay,null as amount_of_goods,null as amount_non_goods, null as amount_discounts,"N" AS ON_HOLD_FLAG from I_0470002  where BU_CODE ='
         || rec_st.BU_CODE
         || '  group by sto_no,cur_code,receipt_no,cash_no,sales_date';

      /*dbms_output.put_line('v_factor->'||v_factor);*/
      /*dbms_output.put_line('rec_st.sto_no->'||rec_st.sto_no);*/

      SELECT DBMS_XMLGEN.getxml (v_large) INTO l_clob FROM DUAL;

      DBMS_OUTPUT.put_line (l_clob);
   END LOOP;
END;
Edited by: 891933 on 31-Mar-2013 21:20
  • 1. Re: plsql and package
    jeneesh Guru
    Currently Being Moderated
  • 2. Re: plsql and package
    894936 Newbie
    Currently Being Moderated
    hI,

    When i running the above mentioned plsql block i am getting not a group by expression error. so i solved that issue.
    but while implementing package i am getting the errors.
    i implemented like below.is this correct or do i need to make any changes.
    CREATE OR REPLACE PACKAGE PKG IS
    PROCEDURE P1;
    END;
    
    CREATE OR REPLACE PACKAGE BODY PKG IS
    PROCEDURE P1 IS
       CURSOR Cur_st
       IS
    .......copied above script...
    DBMS_OUTPUT.put_line (l_clob);
       END LOOP;
    END P1;
    END PKG;
    Edited by: 891933 on 31-Mar-2013 21:27
  • 3. Re: plsql and package
    sb92075 Guru
    Currently Being Moderated
    891933 wrote:
    hI,

    I am getting not a group by expression error.
    which SELECT is throwing the error
  • 4. Re: plsql and package
    894936 Newbie
    Currently Being Moderated
    first cursor part was throwing an error. i corrected by adding sold_amount in group by clause.

    i want to implement the same in package.
    would you please tell me the syntax which i wrote for packe is correct?
  • 5. Re: plsql and package
    894936 Newbie
    Currently Being Moderated
    i am getting this error... when i am running my plsql block.
    ORA-00904: "N": invalid identifier
    ORA-06512: at "SYS.DBMS_XMLGEN", line 176
    ORA-06512: at line 1
    ORA-06512: at line 64
    Script Terminated on line 1.
  • 6. Re: plsql and package
    surendra4y Newbie
    Currently Being Moderated
    is your above query giving dbms_output result ??????????
  • 7. Re: plsql and package
    Manik Expert
    Currently Being Moderated
    Untested but, check if you are using proper quotes inside that select (I observed double quotes instead of single quotes.. (check and confirm))

    Its like you are doing something like

    select "x" from dual;

    instead of

    select 'x' from dual ;



    To be corrected as :
      SELECT SUM (sold_amount) * V_FACTOR as 'INVOICE_TOTAL',
             sto_no AS bu_code,
             'STO' AS bu_type,                               ---------------------observe this I removed the double quotes.
             NULL AS cust_no,
             cur_code,
             RECEIPT_NO,
             TO_DATE (A.SALES_DATE, 'YYMMDD') AS sales_date,
             cash_no AS till_no,
             NULL AS card_no,
             NULL AS amount_advance_pay,
             NULL AS amount_of_goods,
             NULL AS amount_non_goods,
             NULL AS amount_discounts,
             'N' AS ON_HOLD_FLAG                        ---------------------------   modify it this way and give it a try
        FROM I_0470002
       WHERE BU_CODE = BU_CODE
    GROUP BY sto_no,
             cur_code,
             receipt_no,
             cash_no,
             sales_date
    Cheers,
    Manik.
  • 8. Re: plsql and package
    surendra4y Newbie
    Currently Being Moderated
    put 'N' AS ON_HOLD_FLAG
    like ''N'' AS ON_HOLD_FLAG
  • 9. Re: plsql and package
    894936 Newbie
    Currently Being Moderated
    Error at line 1
    ORA-06550: line 56, column 51:
    PLS-00103: Encountered the symbol "STO" when expecting one of the following:
    
       * & = - + ; < / > at in is mod remainder not rem
       <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
       LIKE4_ LIKEC_ between || member SUBMULTISET_
    Script Terminated on line 1.   * & = - + ; < / > at in is mod remainder not rem
       <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
       LIKE4_ LIKEC_ between || member SUBMULT
    when i tried to change the single quotes i am getting the above errors.
    v_large :=
    'select sum(sold_amount)*'
    || v_factor
    || ' "INVOICE_TOTAL" ,sto_no as bu_code,'STO' as bu_type,null as cust_no,cur_code,RECEIPT_NO,TO_DATE (A.SALES_DATE,'YYMMD')as sales_date,
    cash_no as till_no,null as card_no,null as amount_advance_pay,null as amount_of_goods,null as amount_non_goods, null as amount_discounts,'N' as on_hold_flag from I_0470002 where BU_CODE ='
    || rec_st.BU_CODE
    || ' group by sto_no,cur_code,receipt_no,cash_no,sales_date,sold_amount';

    Edited by: 891933 on 31-Mar-2013 22:03

    Edited by: 891933 on 31-Mar-2013 22:06
  • 10. Re: plsql and package
    894936 Newbie
    Currently Being Moderated
    same error. can anybody suggest me on this.
  • 11. Re: plsql and package
    Ashu_Neo Pro
    Currently Being Moderated
    I check your code. If you are passing chars as static in a dynamic sql, then pass it with 2 single quotes. and 2 single quote is not equal to a single double quote.
    Like ''STO''. I have changed it below. try this.
    v_large :=
    'select sum(sold_amount)* '
    || v_factor
    || ' as INVOICE_TOTAL ,sto_no as bu_code,''STO'' as bu_type,null as cust_no,cur_code,RECEIPT_NO,TO_DATE (A.SALES_DATE,''YYMMD'')as sales_date,
    cash_no as till_no,null as card_no,null as amount_advance_pay,null as amount_of_goods,null as amount_non_goods, null as amount_discounts,''N'' as on_hold_flag from I_0470002 where BU_CODE ='
    || rec_st.BU_CODE
    || ' group by sto_no,cur_code,receipt_no,cash_no,sales_date,sold_amount';
    Thanks!
  • 12. Re: plsql and package
    894936 Newbie
    Currently Being Moderated
    Hey thanks a lot. its worked fine.
    now i got the below output.
    step2-->sto_no = '301'
    step7-->sto_no = '301'
    Start
    Fetched factors: 1 and .01
    v_sum------>1.5
    v_LARGE----->select sum(sold_amount)* .01 as INVOICE_TOTAL ,sto_no as bu_code,'STO' as bu_type,null as cust_no,cur_code,RECEIPT_NO,TO_DATE (SALES_DATE,'YYMMDD')as sales_date,
    cash_no as till_no,null as card_no,null as amount_advance_pay,null as amount_of_goods,null as amount_non_goods, null as amount_discounts,'N'as on_hold_flag from I_0470002 where BU_CODE =301 group by sto_no,cur_code,receipt_no,cash_no,sales_date,sold_amount
    L_CLOB----->
    now i want to check how this xml output can be seen.
  • 13. Re: plsql and package
    894936 Newbie
    Currently Being Moderated
    Hi,

    Can you please tell me like how i need to check this c_lob output?
  • 14. Re: plsql and package
    jeneesh Guru
    Currently Being Moderated
    If you are using SQL*Plus
    SQL> var test_clob clob;
    SQL> begin
      2   :test_clob := 'Testing CLOB';
      3  end;
      4  /
    
    PL/SQL procedure successfully completed.
    
    SQL> print :test_clob;
    
    TEST_CLOB
    -----------------------------------------
    
    Testing CLOB
1 2 Previous Next

Legend

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