December 16, 2009 at 3:13 pm
Hi All,
Maybe this is not a right place for this question. Anyhow,
Can anybody try and help me in transforming this FoxPro code to T-SQL ?
parameters pid
set century on
use Table1.dbf in 1
use Table2.dbf in 2
select 1
scan all for recno = pid
*!*scan all
store val(alltrim(field1)) to vuse
Select 2
locate for field2 = vuse
if found()
store field3 to vfield3
store field4 to vfield4
else
store ' ' to vfield3
store ' ' to vfield4
endif
select 1
if field3 <> vfield3 and vfield3 <> ' ' then
replace field3 with vfield3
endif
if vfield4 <> field5 then
replace field5 with vfield4
endif
store ' ' to vuse
store ' ' to vfield4
store ' ' to vfield3
endscan
________________________________________________________________
"The greatest ignorance is being proud of your learning"
December 16, 2009 at 4:41 pm
Probably could if you provided table definitions, sample data, and expected results based on the sample data. Not being a Foxpro programmer, I have no idea what it is doing based solely on the uncommented code.
December 16, 2009 at 9:40 pm
Thanks for replying and please excuse for the poor description before ! This time I'll try to make this code more realistic and descriptive.
Here's what I think this code is intended for:
There are two tables and based upon the common field i.e. CustomerCode in table Customers and Code in table UpdateList it is updating the CustomerName and CustAddress fields in the Customers table i.e if a matching record is found in UpdateList then those two field values in Customers table needs to be updated with the corresponding values from the UpdateList table.
Customers Table: recno(int), CustomerCode(int) , CustomerName(char), CustAddress(char),
UpdateList Table: Code(int), CustomerName(char), CustAddress(char)
parameters pid /*Don't know what purpose this serve. One thought is that this is used to update only the new records. This can be taken care of by selecting records based upon the ImportDate field in Customers table */
set century on
use Customers.dbf in 1
use UpdateList.dbf in 2
select 1 /*using Customers table */
scan all for recno = pid
*!*scan all
store val(alltrim(CustomerCode)) to vVariable0 /* Variable to store this value */
Select 2 /* using UpdateList table */
locate for Code = vVariable0 /*Looking for a matching value */
if found()
store CustName to vVariable1 /*Getting value which would replace the value for matching record in Customers table */
store CustAddress to vVariable2/*same as above comment*/
else
store ' ' to vVariable1 /* if there's no matching record then store blank/null to a variable */
store ' ' to vVariable2/*same as above*/
endif
select 1 /*using Customers table */
if CustName <> vVariable1 and vVariable1 <> ' ' then
replace CustName with vVariable1 /*updating this value with the value from UpdateList table */
endif
if vVariable2 <> CustAddress then
replace CustAddress with vVariable2
endif
store ' ' to vVariable0
store ' ' to vVariable1 /* Clearing the variables. Probably to loop again */
store ' ' to vVariable2
endscan
________________________________________________________________
"The greatest ignorance is being proud of your learning"
December 16, 2009 at 9:48 pm
here's my guess at it:
DECLARE @pid int
--don't see it assigned a value..this might be a select for ALL recs, and pid is the current record?
SELECT ISNULL(CustName,'') As CustName,
ISNULL(CustAddress,'') As CustAddress
FROM Customers
INNER JOIN UpdateList
ON LTRIM(RTRIM(CustomerCode)) = UpdateList.Code -- vVariable0,
WHERE Customers.recno = @pid
Lowell
December 16, 2009 at 10:22 pm
Well, thanks !!
This program is part of another program which stores recno value to a variableA and then it says
Do updatecustomers.prg with variableA
Let me go ahead and post that code as well.
select recno;
from Customers;
where lastupdate >= date()-1
into table temp1 /*Storing recno corresponding to new inserts */
close all
use temp1 in 1
select 1 /*using temp1 table */
store reccount() to vTotalRows /* Storing total no of records into a variable from temp1 table */
close all
i = 1
for i = 1 to vTotalRows /*using a for loop to process all recno's */
use temp1 in 1
select 1
locate for recno() = i
store recno to variableA
? str(i) + ' -- ' + str(vTotalRows) /*No idea on this one*/
do "Y:\UpdateCustomers.prg" with variableA
next i
________________________________________________________________
"The greatest ignorance is being proud of your learning"
December 17, 2009 at 12:44 pm
Lowell,
What's your take on this one?
UPDATE T1
SET T1.CustName = T2.CustName,
T1.CustAddress = T2.CustAddress
FROM Customers T1
INNER JOIN UpdateList T2
ON T1.CustomerCode = T2.Code
AND T1.Convert(varchar,ImportedDate,103) = convert(varchar,GETDATE(),103)
Thanks a lot !
🙂
________________________________________________________________
"The greatest ignorance is being proud of your learning"
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply