sql statement worked in vfp6 but not in vfp8???

[hawkieboy]

Hi All

My employer has just upgraded to fox 8 and i have an application that uses sql statements that worked fine in ver 6. Having a read of recent threads i discover that you have to issue SET ENGINEBEHAVIOR 70 however it would still appear that this does completely solve my problem as the sql is returning strange results. I then discover the following in fox help ;

Using SET ENGINEBEHAVIOR set to 70 might lead to ambiguous results from SQL SELECT commands when the DISTINCT and UNION clauses are used with memos and the GROUP BY clause

Could anyone throw any suggestions my way.

Just out of interest MS changing the way SQL works with in fox is quite an annoying thing to be changed. Is there a reason such a usefull tool has been changed in such a way

Thanks

Nick


your help and advice is appreciated

 

[Mike Lewis]

Hi Nick,

Are you using GROUP BY in your SQL? If so, make sure that the only items in the expression list (the list immediately after the word SELECT) are either items that are also in the GROUP BY clause, or are aggregate functions (such as SUM() or AVG()).

If that's not the case, it is likely that that is the cause of the problem. That situation has always been risky in VFP, and the results are, to a certain extent, unpredictable (or, at least, misleading).

If this doesn't help, perhaps you could post your SQL code.

By default, VFP 8.0 no longer allows the situation I described above, but as you have found, you can relax the rule by issuing SET ENGINEBEHAVIOR 70 -- at your own risk.

I agree that it is annoying that MS have changed the behaviour in this way, but on balance I think it is a good move. The main reason is to ensure that VFP's SQL is compatible with the ANSI standard and thereby to make it more reliable.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[hawkieboy]

Hi Mike

Thanks for coming back so quickly my code is as follows :

SELECT PMDDLADD, PMDMGACC, FULLNAME, COMPANY,    
      PROPER(ADD1), POST_CODE, PMDMGCDE, PMDPYVAS,
      PROPER(STATS), PMDSBSIS,PMDSBEIS, PDSCSACC,   
      PDSRCACC, PMDMGSNO 
      FROM SITPERSUB ;
      INTO CURSOR RESULTS ;
      WHERE (alltrim(pdssname) = 'SMITH);
      GROUP BY PMDDLADD, PMDMGACC, FULLNAME,   
      COMPANY, ADD1,POST_CODE, PMDMGCDE, PMDPYVAS, STATS,  
      PMDSBSIS, PMDSBEIS, PDSCSACC, PDSRCACC, PMDMGSNO ;
      ORDER BY PMDMGACC, PMDMGSNO

I have changed the code so i can drop the set engine... but I am getting records with blank names where i am only expecting SMITH and alike.

Any ideas

Thanks Again

your help and advice is appreciated

 

[craigber]

The best advice is to read the docs. There are topics in the help file about what's new. A must read for every new version.

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com

Author, CrysDev: A Developer's Guide to Integrating Crystal Reports"

 

[jimoo]

I don't see why this code would work in both versions, except there is currently a syntax error in the code. That may have occurred when you dropped it into the tek-tip area.

Check out this line:

WHERE (alltrim(pdssname) = 'SMITH);


Notice 'SMITH)

Shouldn' that be 'SMITH'

Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[hawkieboy]

sorry code is WHERE (alltrim(pdssname) = 'SMITH')

just a typo


your help and advice is appreciated

 

[Mike Lewis]

Nick,

Two points:

- I see that you are grouping by ADD1 and STATS, but that you have PROPER(ADD1) and PROPER(STATS) in your expression list. That might not matter, but strictly speaking in does violate the new GROUP BY rule. It might be worth just seeing what happens if you remove the PROPER()s.

- More importantly, you are doing a grouping without any aggregation. Why not drop the entire GROUP BY clause and add the keyword DISTINCT instead. That should give you the desired result.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[jimoo]

Try changing your groups to reference the position of the fields and see if that helps:

SELECT PMDDLADD, PMDMGACC, FULLNAME, COMPANY,
PROPER(ADD1), POST_CODE, PMDMGCDE, PMDPYVAS,
PROPER(STATS), PMDSBSIS,PMDSBEIS, PDSCSACC,
PDSRCACC, PMDMGSNO
FROM SITPERSUB ;
INTO CURSOR RESULTS ;
WHERE (alltrim(pdssname) = 'SMITH');
GROUP BY ;
1, 2, 3, 4, ;
5, 6, 7, 8, ;
9, 10, 11, 12, ;
13, 14;
ORDER BY 2, 14

One thing in particular that concerns me is that you are selecting the PROPER(add1) but ordering on add1. Proper and add1 are different and that could pimp you.

Prior to Version (I believe) 7.0 you could not use a function, including an "AS" statement, in the order by and group by statements and you had to use the realative postion of the fields.

Ps. Verify I got your field order correct.

Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com