locked
Group by clause is missing or invalid in vfp 9 RRS feed

  • Question

  • Dear friends,

    I am trying to create a view with folloing select command. But it is reporting an error "Group by clause is missing or invalid."

    Where is it going wrong?

    CREATE VIEW vw_itstk as select items.code, items.name, items.op_stock, sum(IIF(it_in_out.ent_tp='I', iif(items.rateperkg,it_in_out.weight, it_in_out.qty),0)) as in, sum(IIF(it_in_out.ent_tp#'I', iif(items.rateperkg,it_in_out.weight, it_in_out.qty),0)) as out from moneymaster!items inner join moneymaster!it_in_out on it_code=code group by it_in_out.it_code

    Friday, April 16, 2010 9:27 AM
    Answerer

Answers

  • Because your group by clause is invalid. It was a bug in VFP7 and earlier to accept such invalid group by clause. Now you need to write the correct SQL instead. As a rule, all the fields listed in select that are not aggregate values should be also part of group by fields (otherwise for example it doesn't know from which record items. Name would come from - as a human you can have luxury to guess there is one items.Name for a given items.Code but you know computers don't make guesses):

    Create View vw_itstk As ;
     select items.Code, items.Name, items.op_stock, ;
     sum(Iif(it_in_out.ent_tp='I', Iif(items.rateperkg,it_in_out.weight, it_in_out.qty),0)) As In, ;
     sum(Iif(it_in_out.ent_tp#'I', Iif(items.rateperkg,it_in_out.weight, it_in_out.qty),0)) As out ;
     from moneymaster!items ;
     inner Join moneymaster!it_in_out ;
     on it_in_out.it_code=items.Code ;
     group By items.Code, items.Name, items.op_stock

    If I were wrong and there might be multiple items.Name per items.code then you could mimic vfp7 behavior in a way:

     Create View vw_itstk As ;
     select items.Code, MAX(items.Name) as 'Name', MAX(items.op_stock) as 'Op_Stock', ;
     sum(Iif(it_in_out.ent_tp='I', Iif(items.rateperkg,it_in_out.weight, it_in_out.qty),0)) As In, ;
     sum(Iif(it_in_out.ent_tp#'I', Iif(items.rateperkg,it_in_out.weight, it_in_out.qty),0)) As out ;
     from moneymaster!items ;
     inner Join moneymaster!it_in_out ;
     on it_in_out.it_code=items.Code ;
     group By items.Code

    Friday, April 16, 2010 9:54 AM

All replies

  • Because your group by clause is invalid. It was a bug in VFP7 and earlier to accept such invalid group by clause. Now you need to write the correct SQL instead. As a rule, all the fields listed in select that are not aggregate values should be also part of group by fields (otherwise for example it doesn't know from which record items. Name would come from - as a human you can have luxury to guess there is one items.Name for a given items.Code but you know computers don't make guesses):

    Create View vw_itstk As ;
     select items.Code, items.Name, items.op_stock, ;
     sum(Iif(it_in_out.ent_tp='I', Iif(items.rateperkg,it_in_out.weight, it_in_out.qty),0)) As In, ;
     sum(Iif(it_in_out.ent_tp#'I', Iif(items.rateperkg,it_in_out.weight, it_in_out.qty),0)) As out ;
     from moneymaster!items ;
     inner Join moneymaster!it_in_out ;
     on it_in_out.it_code=items.Code ;
     group By items.Code, items.Name, items.op_stock

    If I were wrong and there might be multiple items.Name per items.code then you could mimic vfp7 behavior in a way:

     Create View vw_itstk As ;
     select items.Code, MAX(items.Name) as 'Name', MAX(items.op_stock) as 'Op_Stock', ;
     sum(Iif(it_in_out.ent_tp='I', Iif(items.rateperkg,it_in_out.weight, it_in_out.qty),0)) As In, ;
     sum(Iif(it_in_out.ent_tp#'I', Iif(items.rateperkg,it_in_out.weight, it_in_out.qty),0)) As out ;
     from moneymaster!items ;
     inner Join moneymaster!it_in_out ;
     on it_in_out.it_code=items.Code ;
     group By items.Code

    Friday, April 16, 2010 9:54 AM
  • I love that anaswer. Thanx for sucha descriptive answer. I am grateful 2 u. -Ravi
    Friday, April 16, 2010 10:16 AM
    Answerer
  • SQL: GROUP BY Clause missing or invalid What is wrong with below query SELECT order_no, conn_code, descript , sum(qty) as qty, unit_cost, sum(qty*unit_cost) as line_total ; from order_det ; WHERE qty <> 0 AND order_no = order_main.order_no ; GROUP BY conn_code ; ORDER BY conn_code ; INTO CURSOR dd1
    Wednesday, September 14, 2011 12:39 PM
  • With VFP9 and

    SET ENGINEBEHAVIOR 90

    Check in VFP Help SET ENGINEBEHAVIOR Command

    As Cetin said:

    As a rule, all the fields listed in select that are not aggregate values should be also part of group by fields.

     

    SELECT order_no, conn_code, descript , sum(qty) as qty, unit_cost, sum(qty*unit_cost) as line_total ;
           FROM  order_det                             ;
           WHERE qty <> 0 AND order_no = order_main.order_no ;
           GROUP BY conn_code, order_no, descript, unit_cost ;
           ORDER BY conn_code                                ;
           INTO CURSOR dd1
    


     


    Systems Analyst

    Wednesday, September 14, 2011 6:35 PM