×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Fox 2.6 Change Select * statement to Replace All Statement
2

Fox 2.6 Change Select * statement to Replace All Statement

Fox 2.6 Change Select * statement to Replace All Statement

(OP)
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

RE: Fox 2.6 Change Select * statement to Replace All Statement

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

RE: Fox 2.6 Change Select * statement to Replace All Statement

(OP)
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"

RE: Fox 2.6 Change Select * statement to Replace All Statement

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:

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

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

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" 

Tamar

RE: Fox 2.6 Change Select * statement to Replace All Statement

(OP)
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

RE: Fox 2.6 Change Select * statement to Replace All Statement

(OP)
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

RE: Fox 2.6 Change Select * statement to Replace All Statement

Quote:

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

RE: Fox 2.6 Change Select * statement to Replace All Statement

(OP)
Thanks Mike

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close