Fox 2.6 Change Select * statement to Replace All Statement 2

[jlg13]

Hi Experts,

I successfully tested this SQL statement. (it works)

SELECT * FROM Contract WHERE contract.pymt_ddate BETWEEN {21/03/2020} AND {31/12/2030} AND LEFT(Contract.acctno,3)="219" AND contract.bal_due > 0 AND RIGHT(contract.dqstatus,2) <> "LY"

and now I want to convert it to a replace statement and getting a syntax error. I guess I don't know the replace format.

REPLACE ALL contract.pymt_ddate WITH GOMONTH(Contract.pymt_ddate,360) WHERE contract.pymt_ddate BETWEEN {21/03/2020} AND {31/12/2030} AND LEFT(Contract.acctno,3)="219" AND contract.bal_due > 0 AND RIGHT(contract.dqstatus,2) <> "LY"


Can someone point out and correct my flaw?

Thanks guys,
Joe

 

[Mike Lewis]

Joe,

You need to change WHERE to FOR. The former works with SQL, but not with native Foxpro.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[jlg13]

Hi Mike,
I had previously tried that and received Unrecognized phrase/keyword in command. I am wondering if the format of my "Between" command is messing it up??

REPLACE ALL contract.pymt_ddate WITH GOMONTH(Contract.pymt_ddate,360) FOR contract.pymt_ddate BETWEEN {21/03/2020} AND {31/12/2025} AND LEFT(Contract.acctno,3)="219" AND contract.bal_due > 0 AND RIGHT(contract.dqstatus,2) <> "LY"

 

[TamarGranor]

The specific issue is that you're using the BETWEEN keyword that's available only in SQL code, not in Xbase code. If you want to stick with REPLACE, change that section to use VFP's BETWEEN() function:

[pre]BETWEEN(contract.pymt_ddate, {21/03/2020}, {31/12/2025})[/pre]

An alternative is to use the SQL UPDATE command. Then you use WHERE and you don't have to change BETWEEN:

[pre]UPDATE contract SET pymt_ddate = GOMONTH(Contract.pymt_ddate,360) ;
WHERE contract.pymt_ddate BETWEEN {21/03/2020} AND {31/12/2030} ;
AND LEFT(Contract.acctno,3)="219" ;
AND contract.bal_due > 0 ;
AND RIGHT(contract.dqstatus,2) <> "LY"[/pre]

Tamar

 

[jlg13]

Hi Tamar,

I should have noted that I am attempting to execute this from the Fox 2.6 command prompt. I tried the UPDATE "sql" you provided and received "Feature not available"

I then attempted the use the VVFP BETWEEN() function and copied your example from above and received Unrecognized phrase/keyword in command... figuring this was provided thinking I was inserting code into a program vs command prompt?

Here's the line I tried...

REPLACE ALL contract.pymt_ddate WITH GOMONTH(Contract.pymt_ddate,360);
WHERE contract.pymt_ddate BETWEEN(contract.pymt_ddate, {21/03/2020}, {31/12/2025});
AND LEFT(Contract.acctno,3)="219" \;
AND contract.bal_due > 0;
AND RIGHT(contract.dqstatus,2) <> "LY"


Joe

 

[jlg13]

To follow up, I combined Tamar and Mike's post....

I used the VFP between() function
I changed WHERE to FOR

It worked!


Tamar, I would like to understand why the UPDATE didn't work for me and learn something

Thanks guys

 

[Mike Lewis]

I would like to understand why the UPDATE didn't work for me and learn something

Because the SQL version of UPDATE was not available in Foxrpo 2.x (or earlier versions). The native UPDATE command had a completely different syntax and purpose.

On another point, if you have a long or complex statement that gives a syntax error - such as the REPLACE in your original post, the best way to solve it is to gradually remove each optional clause until you have a version that works. For example, if the statement included a clause in the format BETWEEN <value> AND <value>, you would have got a syntax error. Removing that clause would have made the error go away, which would have told you where the problem was.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[jlg13]

Thanks Mike