asked on

Why VFP9 doesn't accept FPW2.6 query ?

Hi Experts

When migrating a query that perfectly runs under FPW2.6 , surprinsingly it doesn't runs under VFP9. Could you find the reason and point out a workaround ?

Thanks in advance !

*-- This code runs fine in FPW2.6 and produces an error in VFP9 - according to the picture
*--- Despesas Discriminadas: Original
	Select  sb_emp,;
		DIA,;
		SB_DGE,;
		this.QDESP(SB_DGE) As DESP,;
		sum(VALOR)    As TOT_D,;
		SB_CCCX,;
		SB_CCTR,;
		"DESP" As SINAL;
		FROM DESP;
		WHERE  Between(DIA, DIA[1], DIA[i]) And;
		sb_emp = m.sb_emp;
		AND SINAL="1";
		GROUP By DIA, DESP;
		ORDER By DIA, DESP;
		INTO Cursor TEMP1_D

*--  Had to be replaced with this 02:
*--- VFP9 - Cursor intermediário (1)
	Select  sb_emp,;
		DIA,;
		SB_DGE,;
		this.QDESP(SB_DGE) As DESP,;
		VALOR    As TOT_D0,;
		SB_CCCX,;
		SB_CCTR,;
		"DESP" As SINAL;
		FROM DESP;
		WHERE  Between(DIA, DIA[1], DIA[i]) And;
		sb_emp = m.sb_emp;
		AND SINAL="1";
		INTO Cursor TEMP1_D0
*------------------------------------
*--- VFP9 - Cursor intermediário (2)
	Select  sb_emp,;
		DIA,;
		SB_DGE,;
		DESP,;
		sum(TOT_D0)    As TOT_D,;
		SB_CCCX,;
		SB_CCTR,;
		SINAL;
		FROM TEMP1_D0;
		GROUP By DIA, DESP;
		ORDER By DIA, DESP;
		INTO Cursor TEMP1_D

The function QDESP and the method this.QDESP are equivalent:
*---------
*---FUNC QDESP
*---------
LPARAMETERS m.forma
LOCAL m.forma

IF seek(m.forma, "DSGER")
   return   dsger.desc
ELSE
   return   space(20)
ENDIF

Open in new window

Gary2Seven

Normally this error indicates that the information the key is created on is not a consistent length.

are you sure that this.QDESP(SB_DGE) is always returning 20 characters?

Eduardo Fuerte

ASKER

Hello

20 characters is only a reference length - I think without relation with the inner problem

It runs perfectly in FPW2.6 and ok too in VFP9 - since the query is broken according to pointed.

ASKER CERTIFIED SOLUTION

Olaf Doschke

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pavel Celba

I agree to Olaf - you should use aggregate functions (e.g. MAX) for columns which are not groupped.

Boths the GROUP BY and ORDER BY can contain column numbers, try:

Select  MAX(sb_emp) sb_emp,;
		DIA,;
		MAX(SB_DGE) sb_dge,;
		this.QDESP(SB_DGE) As DESP,;
		sum(VALOR)    As TOT_D,;
		MAX(SB_CCCX) sb_cccx,;
		MAX(SB_CCTR) sb_cctr,;
		"DESP" As SINAL;
		FROM DESP;
		WHERE  Between(DIA, DIA[1], DIA[i]) And;
		sb_emp = m.sb_emp;
		AND SINAL="1";
		GROUP By 1, 3;
		ORDER By 1, 3;
		INTO Cursor TEMP1_D

Open in new window