none
Group by clause is missing or invalid in vfp 9

    Pregunta

  • Hi, I would like to know how to mimic vfp7 behavior when re-writing the following query in VFP9.

    SELECT a.TranNumb, a.TranDate, a.InvoNumb as RefrNumb, a.CompCode, a.PartCode, 2 AS TranType, ;
    	   SUM(ROUND((b.itemqnty * b.itempack * b.itemrate),2)) + a.StaxAmnt + a.OthrAmnt - SUM(ROUND(b.itemqnty * b.itempack * b.discrate,2)) + a.OthrAmn1 - (ROUND(a.discrate * a.discqnty,2)) AS DebtAmnt, ;
    	   "Sales Bill # "+ALLTRIM(a.Invonumb) as TranDesc, c.PartName ;
    	FROM BillFile a, BillFild b, PartFile c ;
    	WHERE a.TranNumb = b.TranNumb AND ;
    		  a.PartCode = c.PartCode AND ;
    		  a.CompCode = b.CompCode AND &DateCond1 AND &CompCond1 AND &ReptCond1 ;
    	INTO CURSOR TempSale ;
    	GROUP BY a.TranNumb, a.CompCode


    Cheers!

    martes, 05 de febrero de 2013 7:56

Respuestas

  • What would you like to mimic? If you SET ENGINEBEHAVIOR 70  just before the SQL command (and reset it back afterwards) then it will work exactly as you were in VFP 7.

    Of course, correct SQL syntax should be:

    SELECT a.TranNumb, MAX(a.TranDate) TranDate, MAX(a.InvoNumb) as RefrNumb, a.CompCode, MAX(a.PartCode), 2 AS TranType, ;
    	   SUM(ROUND((b.itemqnty * b.itempack * b.itemrate),2)) + a.StaxAmnt + a.OthrAmnt - SUM(ROUND(b.itemqnty * b.itempack * b.discrate,2)) + a.OthrAmn1 - (ROUND(a.discrate * a.discqnty,2)) AS DebtAmnt, ;
    	   MAX("Sales Bill # "+ALLTRIM(a.Invonumb)) as TranDesc, MAX(c.PartName) PartName ;
    	FROM BillFile a, BillFild b, PartFile c ;
    	WHERE a.TranNumb = b.TranNumb AND ;
    		  a.PartCode = c.PartCode AND ;
    		  a.CompCode = b.CompCode AND &DateCond1 AND &CompCond1 AND &ReptCond1 ;
    	INTO CURSOR TempSale ;
    	GROUP BY a.TranNumb, a.CompCode
    or similar. Simply said all fields not included in GROUP BY must be inside some aggregate function (MIN, MAX, SUM, AVG, etc.).

    martes, 05 de febrero de 2013 12:49
    Moderador

Todas las respuestas

  • Hi eCasper,

    Type "SET ENGINEBEHAVIOR 70" in your main.prg.

    • Propuesto como respuesta EnglishBob2Editor martes, 05 de febrero de 2013 9:28
    • Votado como útil eCasper martes, 05 de febrero de 2013 12:09
    • Propuesto como respuesta Pavel CelbaModerator martes, 05 de febrero de 2013 12:47
    martes, 05 de febrero de 2013 8:59
  • Hi, Probably that is not what I was looking for....

    Thanks anyway!


    Cheers!

    martes, 05 de febrero de 2013 12:09
  • What is exactly the problem?
    martes, 05 de febrero de 2013 12:28
    Usuario que responde
  • What would you like to mimic? If you SET ENGINEBEHAVIOR 70  just before the SQL command (and reset it back afterwards) then it will work exactly as you were in VFP 7.

    Of course, correct SQL syntax should be:

    SELECT a.TranNumb, MAX(a.TranDate) TranDate, MAX(a.InvoNumb) as RefrNumb, a.CompCode, MAX(a.PartCode), 2 AS TranType, ;
    	   SUM(ROUND((b.itemqnty * b.itempack * b.itemrate),2)) + a.StaxAmnt + a.OthrAmnt - SUM(ROUND(b.itemqnty * b.itempack * b.discrate,2)) + a.OthrAmn1 - (ROUND(a.discrate * a.discqnty,2)) AS DebtAmnt, ;
    	   MAX("Sales Bill # "+ALLTRIM(a.Invonumb)) as TranDesc, MAX(c.PartName) PartName ;
    	FROM BillFile a, BillFild b, PartFile c ;
    	WHERE a.TranNumb = b.TranNumb AND ;
    		  a.PartCode = c.PartCode AND ;
    		  a.CompCode = b.CompCode AND &DateCond1 AND &CompCond1 AND &ReptCond1 ;
    	INTO CURSOR TempSale ;
    	GROUP BY a.TranNumb, a.CompCode
    or similar. Simply said all fields not included in GROUP BY must be inside some aggregate function (MIN, MAX, SUM, AVG, etc.).

    martes, 05 de febrero de 2013 12:49
    Moderador