SCAN REPLACE WITH SQL JOIN

[erp84]

I have a fairly large SQL query that I'm trying to combine with a SCAN/REPLACE.

SELECT material.orderno, material.pn, material.wc, material.duedate, labor.status, order.status, labor.startdate, labor.pn
FROM   (labor INNER JOIN material ON ((labor.orderno=material.orderno) AND (labor.lineitem=material.lineitem)) AND (labor.wc=material.wc)) INNER JOIN  order ON labor.orderno=order.orderno
WHERE order.status='A''

What I'm trying to do is set material.duedate to the same value as labor.startdate for each record, in the entire recordset from the query. Does anyone have an example they could share? So far my attempts have been embarrassing failures.

 

[Mike Lewis]

I wouldn't do it with a SCAN/REPLACE loop. I'd use a correlated update.

I don't think I can code the whole thing for you right now (it's after 9 pm Friday after a long week), but I'll give you a start:

SELECT material.orderno, labor.startdate ;
  FROM <your existing FROM clause> ;
  WHERE <your existing WHERE clause> ;
  INTO CURSOR csrTemp

UPDATE Material SET DueDate = csrTemp.StartDate FROM csrTemp ;
  WHERE Material.OrderNo = csrTemp.OrderNo

I stress this is off the top of my head, but I hope it will give you the general idea. (You could also check the Help for UPDATE - SQL).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Erp84,

Are you still here? Have you solved the problem? Was the answer I gave any use?

It would be helpful if you could give some feedback on the answer you received, if only for the benefit of others who might have a similar problem.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[erp84]

Hi Mike,

I've been holding down the fort by myself this week so just today I've finally had a chance to attempt this. I just dropped in my appropriate clauses and it works great!

I do see a brief delay before the query status window opens, but that's most likely caused by my environment.

Thank you once again for your help Mike, this one was really frustrating me Friday.

 

[Mike Lewis]

Glad to hear it worked, Earp. I hope you didn't mind my chasing you up.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[erp84]

No at all! I think I'm having the week you had last.