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

Error
Eduardo FuerteDeveloper and AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gary2SevenConsultantCommented:
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?


0
Eduardo FuerteDeveloper and AnalystAuthor Commented:
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.
0
Olaf DoschkeSoftware DeveloperCommented:
There was a change in GROUP BY behavior in VFP8 and later other changes in VFP9. We therefore have ENGINEBEHAVIOR, you can set it to 70,80 and 90. Your legacy SQL should run in 70 mode.

But think about GROUP BY, you only group by DIA and DESP, so in the ANSI standard all other fields of the result must either be aggregated from all records of the group (eg by SUM, MIN, MAX) or must be a computed value. Not grouping by sb_emp, SB_DGE and otehr fields is not allowed. What value should foxpro pick from the group of records?

Up to VFP8, foxpro picked the value of the first record of the group, but that's not a natural. ANSI standard demands you to specify all unaggregated fields in the GROUP BY or leave them out of the result set. Instead you should do a subquery and left join a record corresponding to the group you build by the GROUP BY.

A fast way is to SET ENGINEBEHAVIOR 70. In the long run think about your record grouping.

Bye, Olaf.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pcelbaCommented:
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

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FoxPro

From novice to tech pro — start learning today.