SQL in VFP9

[beedubau]

I'm tearing my hair out!

I have the following in vfp6 which has been running for some years

SELECT DISTINCT idexhibit,I.dsid AS DSID_bw,I.id_person AS tmg_ID,;
		I.xname AS xname_bw,Thumb AS Thumb_a,TEXT AS Text_bw,;
		id_source,I.PRIMARY AS primary_bw,vfilename,afilename,tfilename,ifilename,IMAGE,Audio,Video,OLE_OBJECT,;
		ETYPENAME,T.abbrev,I.rltype AS rltype,;
		A.ref_id AS pr1_num,srnamedisp,DESCRIPT,I.CAPTION,;
		G.ref_id AS Enum,I.REFERENCE AS refer_bw,edate AS evdate,G.RECNO AS RECNO,;
		T.gedcom_tag AS gtag;
		FROM ;
		(TEMP_A AS A	RIGHT JOIN;
		(TEMP_N AS N	RIGHT JOIN;
		(TEMP_T AS T 	RIGHT JOIN;
		(TEMP_G AS G	inner JOIN;  
		TEMP_I AS I;
		ON G.RECNO=I.ID_EVENT;
		AND I.rltype='G';
		AND G.dsid = I.dsid;
		);
		ON T.ETYPENUM = G.etype;
		);
		ON N.Nper=G.per1 AND N.PRIMARY = .T.;
		);
		ON A.per_no=N.Nper;
		);
		INTO TABLE TEMP_5 ORDER BY idexhibit

[code/]

This does not run in VFP9.

Any clues on a more correct syntax?

Regards

Bryan

 

[Mike Lewis]

Bryan,

When you say it doesn't run, do you mean you get an error message? Or it produces incorrect results? Or it just doesn't do anything? Or what?

Also, have you tried executing SET ENGINEBEHAVIOR 70 before executing the SELECT? That won't be a permanent solution, but at least it will tell you if there is something in your syntax that changed when you went to 9.0.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Imaginecorp]

There seem to be quite a few fields that do Not have an Alias, i.e. idexhibit,Thumb etc. IMO, it worked in VFP6 but VFP9 is a little more stringent, try again with the Aliases set...
Good idea to check what Mike suggested first.

 

[beedubau]

Mike,

Thanks for your suggestion - the code does not produce a table but no errors and no syntax complaints.

Will try your suggestion later today.

Regards

Bryan

 

[beedubau]

AS it happened the problem was in previous code - Phew! All old SQL is running cleanly in VFP9 now. I also am understanding my older coding ( 3 years ago) better as well.

Thanks to all

Bryan

 

[Mike Lewis]

Bryan,

All old SQL is running cleanly in VFP9 now.

Glad to hear it.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk