1 2 Previous Next 19 Replies Latest reply: May 14, 2013 10:45 PM by 894936 RSS

    not a group by expression

    894936
      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
        • 2. Re: plsql and package
          894936
          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
            891933 wrote:
            hI,

            I am getting not a group by expression error.
            which SELECT is throwing the error
            • 4. Re: plsql and package
              894936
              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
                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
                  is your above query giving dbms_output result ??????????
                  • 7. Re: plsql and package
                    Manik
                    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
                      put 'N' AS ON_HOLD_FLAG
                      like ''N'' AS ON_HOLD_FLAG
                      • 9. Re: plsql and package
                        894936
                        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
                          same error. can anybody suggest me on this.
                          • 11. Re: plsql and package
                            Ashu_Neo
                            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
                              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
                                Hi,

                                Can you please tell me like how i need to check this c_lob output?
                                • 14. Re: plsql and package
                                  jeneesh
                                  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